Getting wrong answer with Match Function
 

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?

Getting wrong answer with Match Function
 

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

"CB1215" wrote:

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?

Getting wrong answer with Match Function
 

Post your formula.

--
Biff
Microsoft Excel MVP


"CB1215" wrote in message
...
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?

Getting wrong answer with Match Function
 

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)



CB1215 wrote:

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?

--

Dave Peterson