Any legitimate Excel user has used VLOOKUP and knows the syntax by heart. (Lookup Value, Array, Column, etc.) But many of these same users have never used INDEX MATCH before. INDEX MATCH is one of several lookup formulas available in Excel. It has certain features that make it superior to VLOOKUP in many situations. Regardless of which method you think is better, (I have my opinion) it is definitely worth it to learn both formulas and have both at your disposal.
Click here for a detailed explanation of why INDEX MATCH is better than VLOOKUP
Please note that INDEX MATCH is designed for vertical lookups, which is the task that VLOOKUP performs. If you need to perform a matrix lookup, consider using one of the more powerful Excel lookup formula combinations such as INDEX MATCH MATCH, OFFSET MATCH MATCH, VLOOKUP MATCH, or VLOOKUP HLOOKUP.
The VLOOKUP function
First our point of reference: the trusty VLOOKUP formula. VLOOKUP returns a VALUE based on a defined array and column reference. The syntax from Excel is as follows:
=VLOOKUP ( lookup value , lookup array , column , range lookup )
Below is an example of using VLOOKUP to return the value “Shirts” based on the lookup value “1089.”
Our goal was to lookup the Product of ID “1089″ and VLOOKUP did that without a hitch. So how does INDEX MATCH replicate that functionality?
The INDEX function
The basic INDEX function returns a VALUE based on a defined array / column and a row number. The syntax from Excel is as follows:
=INDEX ( array , row number )
Below is an example of using INDEX to return the value “Shirts,” assuming that you already know that the value is three cells down on your defined array.
(you also have the option to specify column number, but that isn’t relevant in a basic INDEX MATCH formula)
If you go 3 cells down in the INDEX array, you get the value “Shirts.” But the problem we have when trying to do a lookup is that we typically don’t know what position our return value is located, which in this case is 3. The “3″ needs to come from another formula.
The MATCH function
The basic MATCH function returns a NUMBER based on the relative position of a lookup value within a defined array / column. The syntax from Excel is as follows:
=MATCH ( lookup value , lookup array , match type )
Below is an example of using the MATCH formula to return the position of “1089″ within our column reference.
Since “1089″ is three cells down in the array, the value “3″ is returned.
INDEX MATCH
When we combine both the INDEX formula and the MATCH formula, the number that the MATCH formula returns becomes the row number for your INDEX formula.
=INDEX ( array , MATCH formula )
Below is an example of using the INDEX MATCH to return ”Product Type” for our lookup value.
As you can see, it returns the same value we got from VLOOKUP.
Differences Between the Formulas
One of the key difference with INDEX MATCH is that, rather than selecting an entire array table, you are only selecting the lookup column and the return column of what would be a VLOOKUP array. While not a big deal when it comes to simple lookups, this can definitely become a factor if you are dealing with large files that have thousands of lookups. By limiting your arrays to only the lookup and return columns, you reduce the processing load on Excel. The difference is illustrated below.
The other key difference is that INDEX MATCH formulas work as a right to left lookup, whereas VLOOKUP only works left to right. As you can see in the example below, I can use INDEX MATCH to lookup a value that is to the right of my return value using INDEX MATCH. This is not possible with the VLOOKUP formula, as you would have to rearrange your data set, or copy your lookup column so that it is always to the left of your return value.
I reversed our formula by looking up the ID of the “Shirts” Product Type. I just changed our lookup value and swapped the lookup and return columns.
Remembering It All
A lot of Excel websites do a poor job of explaining how to use INDEX MATCH. I’m not saying my way is any better, but here’s any easy way to remember how to use the formula, if you don’t want to mess with the detailed explanations and know how to use VLOOKUP. Here’s how I think about the formula as I’m typing it in:
=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0″ ))
Related posts:
{ 70 comments… read them below or add one }
← Previous Comments
Hi,
Thanks for sharing. You are absolutely correct the MATCH& INDEX are powerful.
I, too, bow to your superior knowledge.
Extremely useful, plain english explanation.
Many thanks for this. Is it possible to add an IF function to a MATCH so that it returns say “Yes” if it finds it or “No” if it doesn’t?
Hi Sue,
Try using IF(ISNA(INDEX(Return Array,MATCH( Lookup Value, Lookup Array,0)),”No”,”Yes”)
Best,
Mark
i want to return one value but over 2 different sheets
i have a value that im searching for but it may not appear in sheet 1, but from another sheet?
=INDEX ( 2 Columns I want a return value from sheet 1 and 2 , ( MATCH ( My Lookup Value , 2 Column I want to Lookup against from sheet 1 and 2, Enter “0″ ))
Also fantastic tutorial, made my life easy switching to index match from vlookup!
i have a workbook, with multiple sheets, each sheet has list of items repeated with in a sheet & over all work book, i want to enter value at one place & it must appear all over workbook at once. HELP
Its a vary useful formula. especially I am using it a lot for data maintenance in excel. very helpful post.
Thank you for the example. I’ve recreated it and have been playing with it, trying to learn it.
In my real life case, I have similar data, except that each ID can have multiple rows of different products. I.e., 1089 has a row with Shirts and another row with Coats. I want to search this table and indicate with a yes or no that 1089 has Shirts, or that 1077 doesn’t have Shirts. Can the INDEX MATCH formula work for that?
Thanks for any help!!
This was the ONLY explaination of this that I understood and was able to make work.
THANK YOU. You saved me hours of tedious work.
~j~
Thank you!
Issue: if there is no match, the formula is returning the first value at the top of my column. Any suggestion to get an empty return?
THANK YOU!
After trying unsuccessfully to understand INDEX MATCH using dozens of near-miss explanations, I have had the breakthrough using your trenchant prose. Well done, sir. I have heretofore bought crappy help. Your approach is worth paying for. I shall look for more of it.
Hi, is there a way to configure it to work like a Vlookup which allows you to copy down through multiple rows?
It is also possible, using the full syntax of INDEX, including the optional column reference – INDEX(array,row number,[column number]) – to index an entire array and then pull based on the column number. So if you have a large database and you need to pull several fields from it, you can key the formula once, then cut and paste, changing only the column reference at the end.
So in the example provided above, you could do the following:
=INDEX(A2:C7,MATCH($A$16,C2:C7,0),1) to get the ID (1089) in column 1, and
=INDEX(A2:C7,MATCH($A$16,C2:C7,0),2). to get the Region (OR) in column 2.
I use this all the time.
Many many thanks Mark for making things simple. This is the best ever I have come across so far. It can’t be easier than this. You are a genius.
super boss
using index match to find and order hi to lo values but when 1 value is repeated in the array, the match is only the first cell it finds to match the value ..how do i get a different match to multiple = values
The explanation is very clear on usage of index match & its logic behind. Also, its difference between vlookup. Yours is the most useful one among others that I read. Thank you
Hi Guys,
Firstly I am so glad I found your website. It is all very well explained!! I have tried looking into using vlookups etc but feel that index and match would bet better but just not quite getting it yet.. sadly!!
i am trying to search two worksheets in the same workbook on column Edinbugh and another column for London from a single value in a different workbook.
Something like:
=index(return a value from [worksheet 1] or [worksheet 2],(Match(My lookup Value [different workbook], I want to lookup against worksheet 1 and worksheet 2, Enter “0″))
Please help!!
jsthomson
Great tutorial, easy to follow.
Solved my problem.
Thanks
← Previous Comments
{ 8 trackbacks }