# Index + Match doesn't work with text

1. ## Index + Match doesn't work with text

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???  Register To Reply

2. ## Re: Index + Match doesn't work with text

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)  Register To Reply

3. ## Re: Index + Match doesn't work with text

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.  Register To Reply

4. ## Re: Index + Match doesn't work with text

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.  Register To Reply

5. ## Re: Index + Match doesn't work with text

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  Register To Reply

6. ## Re: Index + Match doesn't work with text

Wow, that was quick. Thanks guys... that worked!  Register To Reply

7. ## Re: Index + Match doesn't work with text

Did you try the shorter version?

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