Excel help: Troubleshooting problems with Index-Match
Index-match is a powerful Excel function, but it can be fiddly. This post explains how to fix common problems.
In a separate post we explain how to use the awesome
**index-match** function in Excel. In this post we’ll focus on what to do when you get stuck.
Check absolute/relative cell references
Matches: Check typos
Matches: Check duplicates
Matches: Check approximate matches
Indexing: Check row/column order
Indexing: Check table/index size
Bonus: Check your checks
Without further ado…
First of all, breathe
It’s ok, we’ve all been there. Breathe. Relax. We can do this.
Check your cell references
One of the most common problems with
**index-match** is mistakes with static and relative references in the formulas.
This will result in incorrect values or error messages when you drag or copy the formulas. The errors are usually easy to spot because you can click into a copied cell and see which area have moved that should be static.
Your static/relative references should look like this:
Check your matches
Typos in matches
Check for typos in your lookup cells: The
**match** functions will look for an exact the match between the lookup value and the row/column values. If you have a typo they will return an error.
Check for duplicate row/column names: Each
**match** function will only return the position of the first match it finds. Duplicate row or column names can therefore be a source of problems.
**match** function takes three parameters (inputs). The third parameter is the
[match type](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a). It can be 1 = less-than-or-equal, 0 = exact, -1 = greater-than-or-equal.
The match type is an optional parameter optional but you unless you know what you’re doing, you should always specify it as 0 because otherwise it will default to less-than-or-equal which can cause problems.
Check your indexing
Indexing rows / columns the wrong way around
Check that in your
**match**functions are the right way around. The first
**match** is for rows, the second
**match** is for columns.
If you get them the wrong way around… bad things happen. 😅
Indexing the same dimensions (common!)
This is a common mistake and I can’t tell you how often I’ve made it myself.
table you index needs to be the same height as your
row index, and the same width as your
Problems are often due to marking the entire
tablebut leaving out an empty row/column header in the
row index or
column index .
This mistake will often give you an off-by-one error. I always mark the table and both index areas from the top left (down or to the right).
That’s it — hope it helps! Let me know in the comments if I missed anything… or if you managed to fix the issue.