Index + Match doesn't work with text

[blueturnaround]

Problem.xls

So the attached sheet, Cell H5 returns a value of N/A. IT should not!!! If I change the lookup value to a number (i.e. "Couple"), it works. But when left as text, it does not. Any way to make this work???

[JBeaucaire]

1) Your MATCH() formulas are missing the 3rd parameter, 0 for exact match.
=IF($F$5=4,INDEX(O7:O10,MATCH($G$5,$K$7:$K$10, 0)),
IF(F5=3,INDEX(N7:N10,MATCH($G$5,$K$7:$K$10, 0)),
IF($F$5=2,INDEX(M7:M10,MATCH($G$5,$K$7:$K$10, 0)),
IF($F$5=1,INDEX(L7:L10,MATCH($G$5,$K$7:$K$10, 0))))))

2) This formula seems to do what you want:
H5: =INDEX($L$7:$O$10, MATCH(G5, $K$7:$K$10, 0), F5)

[MarvinP]

Hi blue,

I did the same thing a few days ago. You left out the third argument in the Match function. You need to make it zero to "match exactly". Try this formula instead:

=IF($F$5=4,INDEX(O7:O10,MATCH($G$5,$K$7:$K$10,0)),IF(F5=3,INDEX(N7:N10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=2,INDEX(M7:M10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=1,INDEX(L7:L10,MATCH($G$5,$K$7:$K$10,0))))))

The reason it gives an error is, without the zero, your data needs to be sorted.

[MrShorty]

I notice that your MATCH() functions are omitting the third argument (see description of arguments here: https://support.office.com/en-us/art...rs=en-US&ad=US ). As explained in the help file, by omitting this argument, Excel assumes a value of 1 for this argument which means that it assumes your lookup column is sorted in ascending order. This is obviously not the case in your spreadsheet. Choose one possible solution a) Add the third argument = 0 to the match functions or (b) sort the tables in K:O by column K in ascending order.

[Pete_UK]

You need to change your MATCH functions by adding ,0 at the end, to make it search for an exact match. The formula becomes:

=IF($F$5=4,INDEX(O7:O10,MATCH($G$5,$K$7:$K$10,0)),IF(F5=3,INDEX(N7:N10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=2,INDEX(M7:M10,MATCH($G$5,$K$7:$K$10,0)),IF($F$5=1,INDEX(L7:L10,MATCH($G$5,$K$7:$K$10,0))))))

Hope this helps.

Pete

[blueturnaround]

Wow, that was quick. Thanks guys... that worked!

[JBeaucaire]

Did you try the shorter version?

=INDEX($L$7:$O$10, MATCH(G5, $K$7:$K$10, 0), F5)

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.