How to Use INDEX MATCH

by Mark Wong on January 16, 2010 · 153 comments

in Excel

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

{ 139 comments… read them below or add one }

JaiNo Gravatar September 7, 2013 at 11:40 am

This is very important and easy way to explain the formula.

JenniNo Gravatar September 13, 2013 at 5:55 pm

Is it just me, or does index-match not play nice when you want to use auto-filters to sort and re-sort the data? Mine get all messed up, in a way that they didn’t when I’ve used vlookup.

RachelNo Gravatar September 19, 2013 at 2:02 pm

How do I use INDEX MATCH when multiple pages/tabs are involved? Is that possible?

Jerry SNo Gravatar October 2, 2013 at 1:37 am

Very helpful, thanks!

hariteshNo Gravatar October 2, 2013 at 4:15 am

Very good explanation..

JasonNo Gravatar October 4, 2013 at 5:15 pm

I am not a regular excel user although I use it a lot for string manipulation and partial ETLs. This posting saved me a lot of time and now I will be switching over to index-match in place of any vlookups. Thanks

Susmita SikdarNo Gravatar October 5, 2013 at 11:05 am

It’s really good one

MahinNo Gravatar October 10, 2013 at 1:32 pm

Very helpful, thank you!!!

R V FirozNo Gravatar October 23, 2013 at 9:42 am

Thanks a ton for this clear and Professional Tutorial on Index Match!!!
Your initiative is highly appreciated!!!

KarenNo Gravatar October 26, 2013 at 12:16 am

Mark,

I happened across your blog/Excel tips while Googling a problem with VLooKup and learned out to use INDEX MATCH. I now use that instead of VLookUp.
Your instructions are very well written, easy to understand and follow; the screen captures are good.
Please post more Excel tips!!

VikramNo Gravatar October 26, 2013 at 6:52 am

great explanation! thank for this!

JackieNo Gravatar October 28, 2013 at 8:10 pm

Correct me if i’m wrong, but if you have multiple result that you want to look at the same references, you’ll have to type:

=Index($A$2:$A$7,MATCH(A16,$C$2:$C$7,0))

FulviaNo Gravatar October 30, 2013 at 10:36 am

This explanation is clear and easy to understand. Thank you!! It will be very useful to me

MarieNo Gravatar November 1, 2013 at 1:20 pm

That’s exactly how I wrote it down in my notebook when I was a grad, many moons ago. And that’s how I used to teach it to my team. So glad to see someone else thinks like me!
Great post too. Thanks!

RutheeNo Gravatar November 7, 2013 at 7:01 pm

I like this function a lot… I would like to know how I can apply it to many different lookup values with out writing an individual formula for each. Any direction?

S ReddyNo Gravatar November 8, 2013 at 10:39 am

Explanation is very clear and perfectly understood

sunil GodboleNo Gravatar November 12, 2013 at 9:00 am

How to use MATCH INDEX for following example:
I have a row: Customer Name and column “Billing to that customer Week”.
Sales amount for each week are recorded.
Say range is c2:k2: Week 1 to Week 9 data.
I want to find out for last how many months customer have not given order (order value=0)
I mean to in reverse order.
Is it possible?

GODFREYNo Gravatar November 13, 2013 at 12:17 pm

is there way to have outputs of abbeverations that begin with B using index/match function in excel2007

MarkNo Gravatar November 19, 2013 at 9:28 am

If the array content same value to wise, Vlookup give the first value as out put But Index and Match Formula give the last value as out put. That a another deference on above.

Zahid Ali Khan SWCC Al KhobarNo Gravatar January 12, 2014 at 6:17 am

Your instructions are very well written, easy to understand and follow. Instead of Range, I used Names, which will be very useful to get values from other worksheets.

PavanNo Gravatar January 13, 2014 at 2:35 pm

Vlookup can also get the expected result even if the output is on the left hand side of the lookup value. Considering the fact that vlookup alone can’t get the desired results but will have to combine with CHOOSE function to look for the value which is on the left hand side of lookup value

Regards,
Pavan

henankshaNo Gravatar January 14, 2014 at 9:04 am

Brilliant explanation! I learnt it so easily from you after struggling because of other explanations. Thanks a ton!!

SeanNo Gravatar January 14, 2014 at 11:18 pm

Awesome!

Karen TNo Gravatar January 23, 2014 at 12:01 am

This was a perfect explanation…thanks!

ChrisNo Gravatar January 27, 2014 at 3:43 pm

This is the standard to which every Excel tutorial should be held. Perfect explanation. Thanks very much.

BradNo Gravatar January 30, 2014 at 10:05 pm

Your tutorial was one of the most helpful, straightforward ones I’ve ever seen. Thank you so much!

JardisNo Gravatar February 17, 2014 at 11:55 am

I’ve red lots of tutorials about this but this is the most clear explanation. Simply the best one. Thanks a lot.

AshleyNo Gravatar February 17, 2014 at 10:50 pm

If the index formula does not match, it pulls back #N/A – how can we get this to pull “NO MATCH” or something along those lines so we know the formula is running, there just isn’t a match.. for those that would be using my file and would question that??

KirkNo Gravatar February 19, 2014 at 12:22 am

Hi Jenni,

Regarding your concern:
“Is it just me, or does index-match not play nice when you want to use auto-filters to sort and re-sort the data? Mine get all messed up, in a way that they didn’t when I’ve used vlookup.”

You need to have the formula reference a table column rather than a specific cell position in the lookup_value portion of the formula. (e.g. =index(‘Sheet 1′!A:A,match([@[ID]],’Sheet 1′!B:B,0),1 instead of ‘Sheet 2′!A2 in place of the [@[ID]])

Hope this helps.

RestyNo Gravatar February 19, 2014 at 1:46 pm

Bravo!

BharatNo Gravatar March 3, 2014 at 1:49 pm

Is it possible to search and list multiple items from an array using one input field thus search down a table and output all the entries that a linked to the single input field.

my formula I am using is :=INDEX($B$2:$B$69,MATCH(H26,$F$2:$F$69,0))

but now I want to increment the output by 1 each time the input appeared.

CadenNo Gravatar March 6, 2014 at 9:03 pm

Very clear, simple so awesome explanation. I pretty much appreciate you.

Naveen kumar singhNo Gravatar March 19, 2014 at 7:54 pm

Hello,
Suppose i have a table, which is includes variable like:_name age sex salary,
and each column include 10 values or more than that. So here,
Question :-
in want to see the largest two person salary including all detail in this way.

salary sax age name,
So here this is the format in which i want to create my report.
Kindly any one come up with a physical answer,

AlbertoNo Gravatar March 20, 2014 at 9:42 pm

Finally I got it. Thanks, Mark Wong.
Regards from MX

SatParkashNo Gravatar March 25, 2014 at 12:31 pm

Awesome explanation !!

RonNo Gravatar March 25, 2014 at 12:31 pm

Agree to your manner of explanation being a very intuitive one. I enjoyed reading your discussion of Index and Match functions.
Thank, you.

RohitNo Gravatar March 26, 2014 at 5:55 pm

Clear Explanation. Very helpful :D Thanks!!!!

JingNo Gravatar April 2, 2014 at 12:27 am

Thank you so much for your succinct and elegant explanation!

JohnNo Gravatar April 3, 2014 at 3:47 pm

Ashley..

If you want to replace N/A with a message just wrap the index match formula like this..

If(isna(…….index match formula…….),”No Match”,(….index match formula….)

Leave a Comment

{ 14 trackbacks }

Previous post:

Next post: