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.
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?
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.
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.
Thank you so much! Brilliant website!
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 🙂
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
Dear Alessio,
I have the same problem. Did you manage to fix your issue and how?
Thank you for the support!
Regards, Madalin
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
Hi Mr. Kuo,
When I use the formula;
=INDEX($E$3:$E$5,MATCH(MIN(SQRT(($F$3:$F$5-I3)^2+($G$3:$G$5-J3)^2+($H$3:$H$5-K3)^2)),SQRT( ($F$3:$F$5-I3)^2+($G$3:$G$5-J3)^2+($H$3:$H$5-K3)^2),0),1)
there is no problem but when I change the return column range from 5 to 86000 for example it gives error. Do you have any idea?
Thanks in advance..
I have a formula I am trying to look for the next match. I do not want to return any blanks. =IFERROR(INDEX(Table_PjM_Active_Project_Tracker__Master___06012017[[#All],[POStatus]],SMALL(IF(Table_PjM_Active_Project_Tracker__Master___06012017[[#All],[PID’#]]=$C$9,ROW(Table_PjM_Active_Project_Tracker__Master___06012017[[#All],[PID’#]])-(ROW(‘Scrub Project File’!$A$2)-1)),ROWS(‘Scrub Project File’!$A$1:$A1))),” “)
This I thought would work but isn’t… any help?
One thing worth another mention that might of saved me a great deal of time was to ensure if using , -1 “Greater than, list must be order descending and if using ,1 should be ascending.
Unfortunately as I was using the same lookup list there was always one side of my range showing N/A.
I was using an index /match to get my range based on criteria between two dates and it took me ages to understand why only one side of the range was working and the other was not working.
Another reason could be that you are looking to match a value in an array that is merged. ie Look for the number 5 in these columns that just happen to be a column of merged cells. You need to reference just the left most cell in the range of merged cells or you might end up with a nasty #N/A.
Another reason why Excel Actually Sucks LOL
I’m trying to look up and extract records from a database, and I’ve gotten this to work in the past. However, this time it seems like it want’s to ignore the date in column B.
Column B – are site codes which I want to use to specify data to extract
Column G – dates that I want to extract between two dates
I use column C for k values (i.e., C21 = 1, C22=2, C23=3 etc.,)
A$3 is the Site code I want extracted. In this case a value of “T”
=IF(C21=””,””,INDEX(‘MM Session Log’!$G$1:$G$500,SMALL(IF((‘MM Session Log’!$B$1:$B$500=A$3)*(‘MM Session Log’!$G$1:$G$500>=9/24/17)*(‘MM Session Log’!$G$1:$G$69<=9/30/17),ROW('MM Session Log'!$B$1:$B$500),""),$C21),1))
I have about 300 line items and the data is in Column B, but no matches are reported. I know this is not correct. I've checked the formatting and the used Match function to verify.
Any help is appreciated.
Ulises Gonzalez
Great! Thanks
I’m trying to find out if it would be a normal reaction for a index match formula to take 11 minutes to complete and maxing out the CPU at 100% the whole time.
I’m using two spreadsheets and trying to match one to the other for the purpose of finding any matches and price changes. One spreadsheet is 6.65 MB and saved as a binary file. It contains 16 columns and 115447 rows. The other spreadsheet is 13.5 MB and binary as well, both because of size. This one has 41 columns and 288259 rows. I am using this formula – =IFERROR(INDEX(‘[199_LAWSON_IC11_PO13_Export 010719 (version 1).xlsb]_199_LAWSON_IC11_PO13_Export_Q’!$B:$B, MATCH($A2,'[199_LAWSON_IC11_PO13_Export 010719 (version 1).xlsb]_199_LAWSON_IC11_PO13_Export_Q’!$H:$H,0),0),””)
I have tried to use all of your information to make a difference but not getting any results. Is there a better way to accomplish this task? Can I change the formula or am I doomed to be unproductive at my job?
Can my lookup value be an entire column? =INDEX(‘Everyday Costs’!H:H,MATCH(WORKING_DEALS!E:E,’Everyday Costs’!B:B,0))
Never referencing an actual cell, I got accurate results. I believe this would make sorting safe. When I reference a cell as my lookup value, then sort the table, my formula gets screwed up. Do you see any reason why above is not going to be accurate or could create errors?
Thanks a lot! It helped me a lot!
My problem is that my reference array end number in my formula doesn’t auto-update when I add new data to extend the range. I have to manually go back into each cell to update the new end number. For example yesterday the end range was A1:B200 but today it would be A1:B300 but the formula is still only referencing up to B200 even if I locked the range as $A$1:$B$300 from the start.