Top Mistakes Made When Using INDEX MATCH

by Matthew Kuo on June 28, 2015

in Database Theory, Error Checking, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

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

IndexMatchErrors_01

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

IndexMatchErrors_02

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.

IndexMatchErrors_03

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.

IndexMatchErrors_04

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.

IndexMatchErrors_05

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.

IndexMatchErrors_06

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 that I helped write several years ago at .  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.

IndexMatchErrors_07

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.

IndexMatchErrors_08

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.

IndexMatchErrors_09

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.

IndexMatchErrors_10

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 formula should work.

IndexMatchErrors_11

You can also use Text to Columns as a solution to this issue, which is detailed in the .

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.

IndexMatchErrors_12

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.

{ 34 comments… read them below or add one }

Erin July 17, 2015 at 12:13 pm

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.

Reply

Scott October 17, 2015 at 7:23 am

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.

Reply

Matthew Kuo October 17, 2015 at 1:31 pm

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

Reply

tommy November 5, 2015 at 1:19 am

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

Reply

DARREN November 17, 2015 at 8:41 am

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

Reply

Mark November 30, 2015 at 5:52 pm

Thanks! Saved me a lot of time!

Reply

Tom January 7, 2016 at 10:18 am

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.

Reply

Matthew Kuo January 9, 2016 at 2:34 pm

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://rusinfomos.ru/excel/how-to-use-if-isna-to-hide-vlookup-errors/

And see below for why IF ISNA is better than IFERROR

http://rusinfomos.ru/excel/why-you-should-be-cautious-about-using-excels-iferror-formula/

Reply

January 20, 2016 at 4:06 am

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.

Reply

Matthew Kuo January 23, 2016 at 3:55 pm

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://rusinfomos.ru/excel/how-to-use-if-isna-to-hide-vlookup-errors/

Best,
Matthew

Reply

amdeen January 28, 2016 at 4:23 am

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

Reply

Matthew Kuo February 6, 2016 at 1:40 pm

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

Reply

Joe February 23, 2016 at 1:11 pm

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

Reply

Mike H October 28, 2016 at 3:48 pm

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

Reply

Joe February 23, 2016 at 1:12 pm

any help would be much appreciated.

Reply

Prakash February 24, 2016 at 4:25 am

Thank you so much for all the solutions…….

Reply

Melba March 4, 2016 at 12:48 am

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

Reply

Des May 3, 2016 at 3:39 am

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.

Reply

mac May 19, 2016 at 1:51 am

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

Reply

eddie May 20, 2016 at 2:27 am

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?

Reply

carl June 1, 2016 at 1:26 am

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

Reply

Kokurai Sutoraifu June 24, 2016 at 1:58 pm

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) |

Reply

Kokurai Sutoraifu June 24, 2016 at 2:06 pm

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 |

Reply

AT October 4, 2016 at 7:29 am

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

Reply

Brayden July 1, 2016 at 6:12 am

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.

Reply

User July 20, 2016 at 11:26 pm

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))

Reply

BS August 13, 2016 at 2:35 pm

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))

Reply

AT October 4, 2016 at 3:00 am

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?

Reply

jolsargon February 11, 2017 at 9:50 am

May i know if your 16,500 UPRNs is a row or a column if because the idea of Index is you can make complex syntax that can search thousands of specific cell but you must be informed that index match as lonng as your id has a unique identifiers and not be duplicated. try index(match(match)) it’s is useful for you.

Reply

Joey November 1, 2016 at 1:27 pm

Is there a way to make this work if you have multiple matches in the LOOKUP column and want to return each value in the RETURN column? I have noticed that it only returns the top match.

Reply

Bart November 1, 2016 at 7:41 pm

Thank you so much! Brilliant website!

Reply

ATraveller November 7, 2016 at 8:50 am

Hello,

I’m trying to use Index Match to generate an array, but my problem is that sometimes it will return the wrong values. Ie. when it’s looking for values 12+1+x, it will return values for 1+2+x … any and all help would be very much appreciated!

=INDEX($F$3:$F$500,MATCH(L$18&L$19&$I20,$B$3:$B$500&$C$3:$C$500&$D$3:$D$500,0))

Cheers 🙂

Reply

alessio TORALDO December 20, 2016 at 6:44 am

Dear all
please help, I am really desperate – the INDEX -MATCH function does not work, and I have tried each and every trick you showed in this nice blog, no way to solve it.
I do not have numbers that were entered manually: the values in the lookup column are the results of a formula. The behaviour of INDEX-MATCH is erratic here, in some cases it does find values which are there, in others it does not, without apparent rule. The same problem I could see, is there if instead of ewntering the values manually in the lookup column, I paste them from another position. That’s really weird and blocked weeks of work. I cannot manually insert the values, as the file I am building is made to analyse unpredictable (and large) datasets. Any help?
Thank you, best
Alessio

Reply

mikkaa January 2, 2017 at 10:25 am

i’m loosing my head. I have index/match formula in one table – working fine. Then i try to use it once again but i get #N/A. If i write in lookup value the number i get correct result. But if i write cell number i got #N/A.
Is it possible that error is because i have repeated values in lookup cell ? Example: C2=20;C3=20;C4=21;C5=22;C6=22….
=INDEX($C$2:$C$29;MATCH(“20”;$A$2:$A29;0)) OK
=INDEX($C$2:$C$29;MATCH(D2;$A$2:$A29;0)) = #N/A

Reply

Leave a Comment

{ 1 trackback }

  • June 28, 2015

Previous post:

Next post:


Related pages


example excel macroformulas for excel spreadsheetwhat are the most used websitesxls vlookup exampleadvanced excel formattingexcel spreadsheet vlookupreplace excel formulaexcel duplicate removerexcel formula for duplicatesgraph a circle in excelrandom name picker excelcheckmark symbol in wordformat for excelinsert tick in word documentexcel randomizecheckmark wordms word tick symboliif excelxls macro tutorialbell curve xlsvlookup compare two columns different sheetsexcel paste values shortcutgood excel booksusing vlook upmultiple if statement in excelhow to make a column graphcharts and graphs excelhistogram binningexcel formula syntaxtest for normality excelmacros with excelucla gpa requirementsneat excel tricksstacked bars excelformulas not working in excelhow to make bar graphs on excelvlookup xlsexcel vlookipadvanced excel tips and trickshow to delete duplicate rows in excelexcel macro codingexcel formula definitionsvba if statement examplehow do you do sums on excelusing nested if in excelexcel vba step by step tutorialpivot tables vlookupsinventory management xls filesexcel vba delete entire rowexcel delete hidden rowsexcel vloopuphow to make a tick mark in excelconditional statements excelusing matrices in excelwingdings check markhow to remove excel formulapaste special values shortcutucla mba programexel countifplotting points in exceltick mark powerpointexcel unique valuesexcel 2007 formulas with examplesexcel vba paste specialexcel duplicate columnsnormal probability distribution excelif with multiple conditions excelms excel lookuptypes of excel spreadsheetshow to sort duplicates in excelhow to reduce rows in excelduplicate values in excelhistogram microsoft excelharvard case study templateexcel indirect function another sheetexcel numbering
\n