The #1 reason why your Excel formula with INDEX and MATCH is broken

Please Hammer Don't Hurt Excel

I've been asked this one so many times I decided to log it here so I can point people to it in future!

The problem: You're looking up data in a table using INDEX and MATCH but the results aren't what you expect.


Almost every time I'm asked why this isn't working there is one simple reason:
MATCH has a third, optional parameter, which if you omit it defaults to a setting that (I bet) you don't want.

The MATCH syntax is
MATCH(lookup_value, lookup_array, [match_type])

In most cases I encounter:
  • You want the result to be an exact match
  • The items in the list you are looking up are not ordered (or not guaranteed to always be in order)

The solution: You need to use zero as your third MATCH parameter.


It's likely that somewhere in your cells you have a MATCH formula that doesn't have zero as the third parameter, so check all those.


If after that, your calculation still isn't working there are a few other things you can do as you try to debug it, as illustrated below:

First, if you've got INDEX and MATCH in one long function, break it down so that you have separate cells calculating intermediate results [see blue and green formula boxes]. 
You can even keep the spreadsheet like this and just hide your intermediate columns/rows, such as columns D and E above 

Second, add a visual means to confirm each lookup is working - one way is to have a cell that performs the same lookup calculation except that it returns back the lookup_value [see grey formula box]
An alternative is to add some conditional formatting to your lookup table to indicate which entries are being looked up [see yellow formula box]

Essentially you want to keep breaking down the problem calculation until you can spot the error.

No comments:

Post a Comment