# 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???

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)

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.

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.

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

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

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

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)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1