Matching error
 

My headers and data are in cells B2-F9. Column headers are the first row (B)
and row headers are in the first column (2). Both are text. The data
(C3:F9) is numerical. I am doing a match function =MATCH(I10,$B$2:$F$2) and
returning "#N/A". Changing the formula to =MATCH(I10,$A$2:$F$2) [simply
starting the match's array to include column A] prevents the error, but
causes the result to inflate the match number by 1.

I am using this in combination with a vlookup
=VLOOKUP("Max",$B$2:$F$9,MATCH(I10,$A$2:$F$2),FALS E) to get a number based on
the intersection of two variables. I can fix it by adding a "-1" to the
'column index number' portion of the vlookup formula but would really prefer
to know why this acts the way it does.

Thanks
--
qwerty

Matching error
 

On Sep 4, 10:49 am, John wrote:
My headers and data are in cells B2-F9. Column headers are the first row (B)
and row headers are in the first column (2). Both are text. The data
(C3:F9) is numerical. I am doing a match function =MATCH(I10,$B$2:$F$2) and
returning "#N/A". Changing the formula to =MATCH(I10,$A$2:$F$2) [simply
starting the match's array to include column A] prevents the error, but
causes the result to inflate the match number by 1.

I am using this in combination with a vlookup
=VLOOKUP("Max",$B$2:$F$9,MATCH(I10,$A$2:$F$2),FALS E) to get a number based on
the intersection of two variables. I can fix it by adding a "-1" to the
'column index number' portion of the vlookup formula but would really prefer
to know why this acts the way it does.

Thanks
--
qwerty

Try MATCH(I10,$B$2:$F$2,0)

You left the 3rd argument blank (same as using a 1), which means the
values must be in ascending order to work properly. Or alphabetical
for text. It seems to give almost random results when this argument
doesn't match the data. I can't tell why.

Use 0 as the 3rd argument for an exact match, with no order
requirement. I presume an exact match is what you want here, anyway.
See the Help file for more details.

Matching error
 

for text. It seems to give almost random results when this argument
doesn't match the data. I can't tell why.

Wait, maybe I do know. Adding the A column to the array probably put
the first few headers (or maybe just one) in some form of alphabetical
order. It will work as far as they are in order, I believe. I'll bet
if you tested every single header in I10, you would find that the
=MATCH(I10,$A$2:$F$2) does not always work, either.

Matching error
 

Spiky - Thanks for the response. I tested it with all the headers and it
worked consistent - just required a "-1" even after I added a "0" for the
third argument. It may be that I am lucking out so I am using Roger's
suggestion as it appears more stable than what I built.
--
qwerty


"Spiky" wrote:

for text. It seems to give almost random results when this argument
doesn't match the data. I can't tell why.

Wait, maybe I do know. Adding the A column to the array probably put
the first few headers (or maybe just one) in some form of alphabetical
order. It will work as far as they are in order, I believe. I'll bet
if you tested every single header in I10, you would find that the
=MATCH(I10,$A$2:$F$2) does not always work, either.

Matching error
 

Roger - Thanks - I will based on the added stability. I hadn't used index
often VS vlookup and match so it was a simple comfort level bias.
--
qwerty


"Roger Govier" wrote:

Hi John

You need to use
=MATCH(I110,$B$2:$F$2,0)
if you want an exact match, or use -1 or 1 dependent upon whether you are
looking for other values.

Why not jut use Index Match
=INDEX($B$2:$F$9,MATCH("Max",$B$2:$B$9,0),MATCH(I1 10,$B$2:$F$2,0))

--
Regards
Roger Govier

"John" wrote in message
...
My headers and data are in cells B2-F9. Column headers are the first row
(B)
and row headers are in the first column (2). Both are text. The data
(C3:F9) is numerical. I am doing a match function =MATCH(I10,$B$2:$F$2)
and
returning "#N/A". Changing the formula to =MATCH(I10,$A$2:$F$2) [simply
starting the match's array to include column A] prevents the error, but
causes the result to inflate the match number by 1.

I am using this in combination with a vlookup
=VLOOKUP("Max",$B$2:$F$9,MATCH(I10,$A$2:$F$2),FALS E) to get a number based
on
the intersection of two variables. I can fix it by adding a "-1" to the
'column index number' portion of the vlookup formula but would really
prefer
to know why this acts the way it does.

Thanks
--
qwerty