Forum Discussion
INDEX MATCH MATCH #N/A Error
I have two tables in Excel called People and Taxes. I lookup the respective tax rate by using INDEX/MATCH and it looks like it works correctly until I add new cities. It looks like it has something to do with sorting the taxes table? I'm curious why the INDEX/MATCH formula doesn't pick up the values correctly even though the formula is referencing the named arrays. See the gif below, what am I doing wrong?
Hi Albert,
This is because you left the MATCH function with the default match mode which is the approximate match.
With this match mode, you must sort the City column in the lookup table from A to Z to get the correct result!
Rather than the Sort, you can switch the function to the exact match mode as follows:
=INDEX(Taxes[Tax],MATCH([@City],Taxes[City],0))
Hope that helps
2 Replies
Hi Albert,
This is because you left the MATCH function with the default match mode which is the approximate match.
With this match mode, you must sort the City column in the lookup table from A to Z to get the correct result!
Rather than the Sort, you can switch the function to the exact match mode as follows:
=INDEX(Taxes[Tax],MATCH([@City],Taxes[City],0))
Hope that helps
Ah gotcha that makes sense. Thanks!
