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.

To summarize:

• 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:

=INDEX($B$3:$E$6,MATCH($B11,$B$3:$B$6,0),MATCH(C$10,$B$3:$E$3,0))

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.

Duplicate matches

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.

Approximate matches

The **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 **index**, 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.

The table you index needs to be the same height as your row index, and the same width as your column index.

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.

Happy Excelling.

To try Curiosity for free, download it at curiosity.ai. Follow us on Twitter or LinkedIn to hear first about new features.