Matching error

[John]

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),FALSE) 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

 

[Spiky]

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),FALSE) 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

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.

 

[Spiky]

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.

 

[John]

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.

 

[John]

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

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(I110,$B$2:$F$2,0))

--
Regards
Roger Govier

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),FALSE) 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