Getting wrong answer with Match Function

[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?

 

[Sheeloo]

Need the formula and sample data before making any comment...

 

[T. Valko]

Post your formula.

 

[Dave Peterson]

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)