*To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon*

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

*Solution:*

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.

**Solution:**

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.

*Solution:*

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.

*Solution:*

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.

*The next set of problems come from a post about VLOOKUP errors that I helped write several years ago at Randomwok.com. The following error checks can be applied to both VLOOKUP and INDEX MATCH.*

**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.

*Solution:*

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.

To summarize:

** **

** • 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.

*Solution:*

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.

*Solution:*

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.

{ 28 comments… read them below or add one }

I really appreciate the concise explanation of INDEX & MATCH. I was recently on an engagement where solely V-Lookup was used and produced a couple of ‘errors’ because there were a few instances where the lookup had technically two values that were correct responses. If INDEX & MATCH were used with conditions, it could have explicitly provided the best response.

I am creating a spreadsheet where I am using a City name to match the name in another database and return a population number. I am getting a error message. Is it because I am using text to text match to return a number? Does the Index to Match require numbers to calculate. If so is there a way to turn text (city names in this case) to numbers so that the formula will calculate?

Thanks.

Hi Scott,

Text matches should still work with INDEX MATCH. I would just make sure what you’re looking up and what you are looking up against are both formatted the same way (both as text). Also, you might have a trailing space after one of your city names that is causing it not to match.

Best,

Matthew

Hey, excellent info. Thanks for taking the time to add it!

DONT forget to enable calculatns, wasted alot of time thinking i had this worked out incorrectly

Thanks! Saved me a lot of time!

How can I leave the cell blank if there is no match?

I have done it with VLOOKUP and it makes the spreadsheet much neater.

Hi Tom,

You can do this with either the IFERROR function or the IF ISNA formula combination. My preference is the latter. See below for how to use the IF ISNA function

http://www.mbaexcel.com/excel/how-to-use-if-isna-to-hide-vlookup-errors/

And see below for why IF ISNA is better than IFERROR

http://www.mbaexcel.com/excel/why-you-should-be-cautious-about-using-excels-iferror-formula/

How can I leave an email address (or number) if there is no match?

I am using the formula to pull up a sales person’s email based on zip code and when the zip code is outside our territory, then I want to send the email to the admin.

Hi Peter,

Below is a link to a tutorial covering how to use logical structuring with your lookups, and putting in a custom message if there is no match. Rather than using “not found”, put in the email address that you want.

http://www.mbaexcel.com/excel/how-to-use-if-isna-to-hide-vlookup-errors/

Best,

Matthew

Is it possible to use the matching function to look at multiple variables IN ONE CELL? I am trying I am trying to match one reference no with other cell .

Example :

My reference :12345

I want to match with same ref number in cell “ie” Motor,PN:12345,mfr:xxx

Hi Amdeen,

Yes, it should be possible to lookup on whether or not a cell just “contains” what you are referencing. For the situation you are describing, you would just put a star before an after your lookup value. These are what Excel calls “wildcards”. So your lookup value would look like this:

*12345*

Let me know if that helps.

Best,

Matthew

Hi I want to get the login time of person on certain date, ex: below. What is the best formula to use and if no matches can if be blank for the return?

Column A – Name

Column B -date

Column C-Time

Probably easiest to do in VBA, and alternative solution in VBA below (Very simplistic, didn’t put too much time into it) that will filter a table of values by name and date based on values entered into a cell:

Sub FilterOnVals()

‘

‘ FilterOnVals Macro

‘

‘

ActiveSheet.Range(“$L$16:$N$20”).AutoFilter Field:=1, Criteria1:=ActiveSheet.Range(“$L$23”).Value

ActiveSheet.Range(“$L$16:$N$20”).AutoFilter Field:=2, Operator:= _

xlFilterValues, Criteria2:=Array(2, ActiveSheet.Range(“$L$24”).Value)

End Sub

So If I entered the name of the person (as it appears in the table) in cell L23, and the Date in question in L24, then running this macro would filter down to that name and date, leaving only times for that combination of values visible. Could tie that to a button, and then record a macro to clear filters, and have that as a second button – then you could do look-ups/clear look-ups with 2 buttons and a couple of cells.

With some research into some basic VBA, you could write one that prompts for the values to look for and returns what you want rather than going the filter route, etc – given some time and thought it wouldn’t be too difficult to develop, but wanted to give you an idea of how to approach a problem like this one where a 1 to 1 lookup isn’t enough.

There is another article that promotes creating a lookup cell with concatenation of the two lookup cells into an additional column, and then using that as your index/match criteria – this is another solution that would be easy enough to implement; it just depends on whether you want an extra column for this functionality, or want to use a filter, or want to code it rather than use formulae.

Best of luck

any help would be much appreciated.

Thank you so much for all the solutions…….

Hi! Thanks, this was helpful! Also if you guys are using INDEX-MATCH-MATCH, the look-up value for the second MATCH should be locked to the column, not the row. 🙂

Hi,

I am trying to create a match formula to match to a cell containing text and then the second cell contains a formula where the result is the same as the text.

Any help would be greatly appreciated.

Yes!!!! Exact match was ruining my world. Many thanks.

the first set of matches work but as soon as I change customer names I only return the first record can I send you the sopreadsheet?

My number one problem is that the internal date format of excel makes it really hard to use the MATCH function on date arrays. How to do that? Spent several hours on this “one minute problem”.

Hello, I’m in need of some assistance with my formula. I have two workbooks (wb): Source.xlsx and Mapping.xlsx. The Mapping wb indexes data from the Source wb and finds the exact name of the column from Mapping and displays the match from Source.

Problem:

With my current data, the formula works fine, but if I add a new column to the Source wb, then the formula is off and displays data that is 1 cell lower than the desired cell. I added the “-1” at the end of the index row number part of the index formula because without it, it also displays data that is 1 cell lower than the desired cell. What I end up having to do to fix the formula is that every time I need to add a new column to the Source wb, I have to change the formula at the end to -2 or -3, depending on how many rows are added. So right now, with 3 columns of data (Test1, Test2, Test3) the formula utilizes -1, but if I add a 4th column, then I have to change it to -2. Is there a way to change the ending row_number section of the index formula so that I don’t have to update it manually and so that every time a new column is added, the formula on the Mapping wb automatically recognizes it and adjusts accordingly?

My formula:

=INDEX(‘[Source.xlsx]MK’!I1:I$72,MATCH($B$9,'[Mapping.xlsx]MK’!$A$6:J$6,0)-1)

Column i, cell i6 is titled “Function” in Source

Cell B9 is also titled “Function” in Mapping

Source Columns A-J (Starts on Row 6):

Section | Line | Field Name | Test1 | Test2 | Test3 | First Test Values | Function | File |

1 | 2 | 3 | 4 | 5 | 6 | 4 | Test1 | Test1 |

Mapping Columns A-B (Starts on Row 9)

# | Function |

1 | (See formula above) |

To clarify and correct:

“I have to change the formula at the end to -2 or -3, depending on how many rows are added”

Should be:

“depending on how many columns are added”

Source Columns A-J (Starts on Row 6):

This is the Columns:

Section | Line | Field Name | Test1 | Test2 | Test3 | First Test Values | Function | File |

This is the row of data below the titles above:

1 | 2 | 3 | 4 | 5 | 6 | 4 | Test1 | Test1 |

Dear Kokurai,

You could try transposing the data that relates to the last part of your formula so it appears in one column instead of over several columns, even if in another tab… is this workable with the data you have?

AT

Hi

I had an issue with index match but the problem was not listed here.

I used a cell reference as my lookup value, it worked in all other cases but for some reason, when I used the actual value, the formula worked.. but when I used a reference cell in the formula (which had the exact same value) it did not work.

I still cant figure out why.

It seems different starting indices produce errors

This case reliably produces Off-By-One-Errors when using MATCH.

=INDEX(B:B; MATCH(G4; B2:B50; 1))

Another source of errors are the parameters 1 and -1.

1 needs the list of numbers to be sorted in ascending order (!!!) and grabs the first value which is smaller or equal to the searched value.

-1 needs the list of numbers to be sorted in descending order (!!!) and grabs the last value which is bigger or equal to the searched value

This enables a hack to get the last (positive) number in a column before the empty cells start (empty cells between numbers are ignored):

Every number in the column has to be bigger than 0 for this to work.

=INDEX(B:B;MATCH(0;B:B;-1))

Need help. I have a formula that is more complicated than this but just for simplification purposes use the example above. What if in the above situation there were 2 IDs with the same number but from different states? How would I get the formula to return the first state and then the second state? My formula just returns the same result both rows because the ID number is the same. It wont pick up the second state. Below is my formula.

=INDEX(Pieces!C$6:C$515,MATCH(1,INDEX((Pieces!$D$6:$D$515=$B53)*(Pieces!$G$6:$G$515=$R53),0),0))

I am trying to use Index Match to deliver a value in the normal way. I have it working fine in numerous places in my spreadsheet, but not with this example.

I have a list of 16500 UPRNs as references (the match part) and a list of 5 area numbers as the information I need the Index Match to deliver (the index part). I have exhausted all of the good suggestions above and it still delivers ‘#N/A’, even though I can find the reference in both match lists! Is the problem here that I have 16500 UPRNs? If so, is there a better function to use for this size of dataset? If not, do you know what the problem could be?

{ 1 trackback }