How to Use INDEX MATCH

by Mark Wong on January 16, 2010 · 300 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.

Excel Bestsellers

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.”  (Please note that, because we input false for that portion of the syntax, we are not using the range lookup feature of VLOOKUP in this example)

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

If you’re still having trouble with INDEX MATCH, click here to learn about the most common mistakes made when using the formula combination

{ 35 comments… read them below or add one }

LindaNo Gravatar July 22, 2015 at 5:28 am

This is magic! Thanks for explaining it so well.

NinaNo Gravatar July 22, 2015 at 4:23 pm

Hi,
Could you please help me with excel, please? At the moment, I’m trying to find a “value” in an array with 3 variables as the criteria. The criteria are “seed number”, “WTC”, and “output type”. The representation of the raw data is like as follow:
Seed number 1 1 1 1 1 1 2 2 2 2 …
WTC 10 20 30 10 20 30 10 20 30 10 …
Output type A B A B A B A B A B …
Value 1 2 3 4 5 6 7 8 9 10 ..

I have tried to use Hlookup, match and index, but they are failed :(.
Please help me….thank you…

AshishNo Gravatar July 22, 2015 at 5:52 pm

How can I use loop in “My Lookup Value”? I have to insert the data in a column so I want to use a single Index formula which will add the value in all rows of a column.

I am using formula like :
=INDEX(H4:H1142,MATCH(T4,G4:G1142,0)) …where I want to add the value in all rows i.e T4 to T1200 but how to add the loop so that using single formula I can do the insert my values from the entire data.

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

Much Appreciate the help if any one can solve it.

Thanks,
Ashish Gupta

Pyae SoneNo Gravatar July 24, 2015 at 7:01 am

Thanks a lot, this is very useful for my work.

colamityNo Gravatar August 1, 2015 at 6:24 pm

You’re the bomb. Very simple instruction. Thanks a million!

Cool_NielNo Gravatar August 2, 2015 at 6:13 am

Thanks for explaination,
I have on1 question, why will you use 2 formulas to perform 1 task which you can do in 1 formula

katyNo Gravatar August 5, 2015 at 11:48 am

Great Article. Thanks for the info. Does anyone know where I can find a blank “2012 USCIS I-130 Instructions” to fill out?

Barath ANo Gravatar August 7, 2015 at 1:18 pm

It could be better, if the example is for two match case value returns a answer,

For Example:

Person Name / Father Name / Mother Name

If i want to find out the person name for specific mother name and father name, can i use the formulae?

Jay BurnNo Gravatar August 25, 2015 at 10:46 am

Good article, and I just wanted to point out the circular error… your article “Why INDEX MATCH is Better Than VLOOKUP” points to this article to read and understand first, but when you get here, before you get into the process, you redirect back to the “Why INDEX MATCH is Better Than VLOOKUP” article again… after a few loops my processor burnt out and needed coffee.
😉
Thanks for the articles, good reads.

(Disclaimer – for any out there that missed it, that was a joke)

KrishnaNo Gravatar August 26, 2015 at 5:08 am

You made it very simple, I am gonna use this formula for me…seriously a great help…..

Kamlesh Kumar ChoudharyNo Gravatar September 9, 2015 at 11:22 am

***What is the use of less than (1) or greater than (-1) in Index Match Function??? Please help me out? That function is far better than works instead of VLookup bcuz it works both side…

MahmadNo Gravatar September 19, 2015 at 3:33 pm

Thank you very much for this article (stumbled upon), simple and easy to understand
I am usually a VLOOKUP user,now am considering switching to the INDEX/MATCH formulae.
Wishing you all the best.
Bye.

Amit LalwaniNo Gravatar September 23, 2015 at 2:28 pm

Thanks a lot Mark.

Really a great way of explaining this formula.

Now I know how to use Index Match effectively.

Regards,
Amit

GiftyNo Gravatar September 28, 2015 at 7:41 am

Tans you very much

AggieNo Gravatar September 29, 2015 at 10:08 am

Your explanation is great! I finally understood what INDEX and MATCH is for. Thank you so much for it. Saving your website to my favourite as I will be back! Very very good job your’re doing.

Rodney MuhammadNo Gravatar October 1, 2015 at 5:05 am

Excellent examples and EXCELLENT Instruction. I fully understand now. A+

amyNo Gravatar October 7, 2015 at 4:40 pm

Thank you. I found this helpful, but what if neither index or match array are unique. I’m having issues getting values.

NKNo Gravatar October 8, 2015 at 2:24 pm

Hello. Thank you for this article. Is there a way to refer to specific cell in a Named Range in an INDEX/MATCH formula? For example If I Created a Named range for the column that contains my lookup value, how could I refer to a cell in this Named Range in the formula?

Thanks

Teresa WhelanNo Gravatar October 12, 2015 at 3:48 pm

THANK YOU for finally coming up with an easy-to-understand explanation of a formula with examples that I can follow! I used the IFERROR with the INDEX-MATCH formula and it worked perfectly when VLOOKUP would not. Thanks again!

TroyNo Gravatar October 14, 2015 at 4:57 pm

Thanks so much for this site – very helpful. I was wondering if you could assist in taking this a step further. These examples base the search criteria off of a single criteria but what if you needed two? My example is matching a county to website for that county. I found that when doing this across the country many different states have the same county names. So what happens now if I don’t only want to base my search off the county but the state and the county? Thanks again – I’ve searching countless websites and you’re was the one that explained it perfectly.

Regards

hrxNo Gravatar November 2, 2015 at 5:57 pm

what is enter ? Why this zero?

private storage gold iranNo Gravatar November 9, 2015 at 4:10 pm

Excellent post. I ussd to be checking conatantly
this blog and I am impressed! Extremely useful info specially the last phase :) I handle such info a lot.

I was seeking this certain information for a long time. Thank you and best of luck.

kbsNo Gravatar November 14, 2015 at 6:18 am

Tried using this formula but excel says too few arguments and isnt executing.

video reviewNo Gravatar November 15, 2015 at 5:49 pm

You are so cool! I don’t think I’ve truly read through something like this
before. So wonderful to find another person with unique thoughts on this issue.
Really.. many thanks for starting this up. This web
site is something that’s needed on the web, someone with a
little originality!

Commentdevenir-Riche.netNo Gravatar November 22, 2015 at 3:34 pm

Hi, i fewel that i saw you visited mmy web site so i got here to return thee desire?.I am attempting to to find things
to imprrove my website!I guess its ook to use a few of your
ideas!!

JingNo Gravatar November 26, 2015 at 3:59 am

Thank you so much for the explaination. It really helps.

ChakravarthyNo Gravatar November 27, 2015 at 7:25 am

Good explanation… and the last para is sooo true… Keep rocking :)

MarguiNo Gravatar November 30, 2015 at 7:39 pm

Great explanation 😉 I learned how to use vlookup for work then I saw I also needed to know the hard old way that many talk about. Thanks!

louNo Gravatar December 5, 2015 at 12:57 am

thank you so much for your clear explanation of how to think about constructing the combined index match formula. i have done it in the past, but struggled when needed at a later date after a period of time had past. this made it all so clear and i nailed it the first attempt:

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

RoNo Gravatar December 9, 2015 at 6:28 am

This is the first time I’ve understood the INDEX MATCH formula combo to an extent that I can use it. Thanks-a-million!

I want to be able to look up a figure based on the results of two columns and I think this is the path to get there. Still trying to solve this one.

D ThiyagarajanNo Gravatar December 14, 2015 at 11:12 am

Very simple explanation, I never understand the INDEX and MATCH before in so many author’s articles, But this is very very superb and simple explanation of my basic knowledge of excel.

Nicky CNo Gravatar December 18, 2015 at 11:43 am

Fantastic explanation. I really like the way you split the two bits of the index and match function out before you explained how they worked together.
I can see why Excel would prefer this – I shall be using this from now on instead of vlookups!

Corrine AldridgeNo Gravatar January 16, 2016 at 3:10 am

Amazing post; thanks for your help!

saifuzamanNo Gravatar January 18, 2016 at 3:41 am

Great. You teach us very well. Thumbs Up.

PeterNo Gravatar January 28, 2016 at 1:28 am

Hi Mark,

Really nice article and helpful, easy to read information. Thank You :)

Leave a Comment

{ 15 trackbacks }

Previous post:

Next post: