Getting wrong answer with Match Function

  • Thread starter Thread starter CB1215
  • Start date Start date
C

CB1215

Excel 2007, HP PC. I have a long array of data, with a varying number of
entries per each individual on whom training data is kept, based on what
events they have not completed. I use a simple formula in a column to the
left of the array (column A) to put a sequential number by the first entry
for each individual. I am using the Match function (within an Address
function) to search column A to find the first row of a person's individual
entries. The error I am encountering is that the Match function occasionally
returns the wrong row number! For example, if the first entry for the 6th
individual is actually on row 190, I've gotten 180 as the answer when I step
through the formula audit. Any thoughts?
 
Make sure you tell excel that you're looking for an exact match.

=match(a1,sheet2!a:a,0)
(that 0 (or False) forces an exact match)

And if the lookup value contains a wildcard (?, *, or ~), then that could cause
trouble.

=match(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!a:a,0)
 
Back
Top