INDEX MATCH Not Working? The following article provides the most likely causes of your problems with using INDEX MATCH.
If you’re an advanced user of Excel, you’ve likely already made the switch from VLOOKUP to INDEX MATCH because of the several advantages that INDEX MATCH provides. Within Excel, INDEX MATCH is one of the most prevalent formula combinations available. Despite this popularity, INDEX MATCH is by no means perfect and is still prone to errors. The following examples represent the most common mistakes made when using the formula combination.
You Flipped the Syntax
Even being one of the biggest proponents of INDEX MATCH out there, I still make this error occasionally. From a logic perspective, the syntax of INDEX MATCH is somewhat backwards. You’re supposed to reference your return column first, then reference your lookup value and lookup column.
It’s understandable that, if you were to try to work this out intuitively, you might reference the lookup column first and the return column second because generally, that is how your data will be setup. See an example of the error below:
There really is only one way to prevent this error: repeat the process several times until you nail down the syntax in your head. I helped write the article over at Randomwok.com, which has a great “worded” syntax to help you remember it:
=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))
If you’ve written it correctly, the logic should feel somewhat backwards, as you input your return column first, then your lookup column afterwards.
You Forgot to Indicate Exact Match
One of the annoying things that has changed about Excel formula syntax is that, when you forget a component of the syntax, Excel will occasionally make an assumption on your behalf and return a value for you despite your omission. While this has the potential to can save you time in the formula writing process by reducing the number of inputs you need to make, the assumption Excel makes is not transparent and is in most cases, incorrect. As a personal gripe, I’d much prefer Excel to show me an error message when I’ve forgotten pieces of syntax, rather than making an assumption on my behalf.
When it comes to INDEX MATCH, this assumption error occurs in the MATCH portion of the syntax. If you input INDEX MATCH without the “0” in the MATCH formula, to indicate that you want an exact match, Excel will assume that you want to input a “1” instead of a “0”, and will return a result for you. With the value “1” in the MATCH syntax, you’re telling Excel that you want Excel to find the largest value less than or equal to your lookup value. For lookups, this is assumption and the resulting output is clearly wrong.
In the example below, the return value for “5158” should be “TN”, but because we forgot to indicate exact match, it returns “MN” instead.
The best way to prevent this error is to leverage the help text that pops up when you are writing the INDEX MATCH formula. Once you get to the MATCH section, it will clearly display all the different pieces of syntax you need (lookup value, lookup_array, match_type) and highlight them in bold when you get to the point you’re supposed to input them. Also, once you get to the match_type input, a useful pop-up appears and shows you both your input options as well as a description of what each input does.
You’re Reference Arrays Are Not the Same Size
One disadvantage of INDEX MATCH relative to VLOOKUP is that you have to make multiple selections. When dealing with small data sets, this task is relatively easy. However, if you’re dealing with a large data set or database, where you can’t see all the way down your columns in a single screen, this mistake is much more likely to occur.
The easy fix to this is to check the numbers on your array references. If you’re using INDEX MATCH to do a vertical lookup, the numbers in your references should be exactly the same and should extend all the way down your lookup table. If they aren’t the same, then you have an error in your lookup formula.
In the example above, the return column reference should be “E4 : E15” and the lookup column reference should be “C4 : C15“.
The Lookup Value You’re Using Isn’t Actually There
While this may seem like one of the most obvious mistakes to catch, it’s also usually the last the thing we look for during the process of error checking. There’ve been countless times when we’ve made this error, gone through every other error check I know of, then realize that that the value we were looking up wasn’t even there in the first place. If you’re working with bad data or a broken business process, this issue can happen more frequently than you expect.
A simple way to check for this error is to use the MATCH formula by itself and see if it returns a value. Doing so is useful when you’re still learning Excel because it isolates a smaller portion of the INDEX MATCH formula, and the shorter length reduces the chances of making additional errors. If you’ve written your MATCH formula properly and you don’t see a return value, then you know the value isn’t there.
You Forgot to Reference Lock
Any time you intend to drag a lookup formula down or across, you have to consider if and where you need to reference lock the arrays within your lookup formula. If you forget to reference lock, your arrays will move with the direction of your dragging and will likely end up producing an error.
The first step is to note which way you are dragging, as what you lock will be different depending on the direction. As illustrated above, the most common way of dragging an INDEX MATCH formula is to drag it vertically in order to pull return values for multiple return values.
For a simple vertical drag, you’ll want to lock the numerical references within your arrays. Do this by adding a “$” symbol in front of each of the numbers for both your Lookup Column and your Return Column. Please note that, in this situation, you DO NOT want to reference lock your Lookup Value, as you need it to float vertically, referencing new lookup values as you copy the formula down.
A more complex but still common scenario is needing to drag your formula both down and across. This is usually done to recreate portions of a table, but using a smaller set of lookup values. In the example shown below, we want to recreate the same “Name” and “State” columns, but only for 4 values rather than all 12.
For a vertical and horizontal drag, it makes sense to think about each piece of the syntax individually as the reference locking is slightly different for each one of them. Starting with the Lookup Column, you don’t want this reference to float at all, so make sure to lock the reference both vertically and horizontally. You can do this quickly in Excel by hitting F4 while the reference is selected. For the Return Column, you need this reference to float horizontally to capture both the “Name” and “State” columns, so don’t put anything in front of the letter references. However, you don’t want it to float vertically and should therefore place a “$” symbol in front of the numerical references. Finally, with the Lookup Value, you need this reference to float vertically to capture the different “IDs” in your table. However, you don’t want it to float horizontally, because then it would start referencing your return values. Therefore, put a “$” symbol in front of the letter reference.
• Lookup Column – lock both vertically & horizontally ($ in front of letters & numbers)
• Return Column – lock horizontally ($ in front of numbers)
• Lookup Value – lock vertically ($ in front of letters)
If and when in doubt, just use the locking scenario above, as it will work for the vast majority of dragging scenarios.
You Have an Extra Space Within Your Values
When downloading data from certain source systems, sometimes that source system will append an extra space to your values. If you an extra space in your reference values, clearly your exact match lookup will not work.
A quick way to solve this issue is to use the TRIM formula to remove the extra space from your values. The approach and syntax are both fairly simple. Just create a new column and reference your original values with the TRIM formula. The new column will no longer have the extra space and your INDEX MATCH formula should work.
You can also use Text to Columns as a solution to this issue, which is detailed in the prior post.
You Have Your Numbers Formatted as Text
Another common annoyance that can occur when moving data around between source systems is having your numbers formatted as text. If your lookup value is formatted as a number, but the column you’re referencing against is formatted as text, Excel will return with an error message.
With the latest version of Excel, when you have numbers formatted as text, Excel will call it out for you with a green error indicator in the upper left hand corner of the cell.
Sometimes people will purposely add an apostrophe in front of a number to format a number as text to, just so they can add a leading zero to that entry. However, because this will likely cause errors down the road, it is generally recommended to use custom number formatting to add leading zeroes to your numbers.
Fixing this error is relatively simple. As you can see in the error message above, Excel gives you the option to “Convert to Number” for the cells in question. Therefore, you could simply select this option for all of your erroneous values. Please note that if you have multiple erroneous values, you can use the following steps to correct them all at once:
• Highlight all the erroneous cells at once
• Open the error menu for any one of those cells (indicated by the yellow caution sign)
• Select the “Convert to Number” option
Another simple workaround is to simply multiply all of your number formatted as text values by “1”. Yes, you can still multiply numbers formatted as text, you just can’t lookup against them. The “multiplication by 1” trick allows you to convert the text into a number without changing your intended value for the cell.