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


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

{ 240 comments… read them below or add one }

anhleNo Gravatar April 8, 2015 at 2:17 am

Thanks for your instruction. I found it very helpful. I could use your instruction to do my excel job. Hope that u will have more useful excel instructions for sharing with us.

DeasyNo Gravatar April 10, 2015 at 4:58 am

when you wrote :
=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))
i said this is genius, for me who doesn’t really understand much about excel , it this is just perfect, very understandable. At the beginning i’ ve got confused, but when i saw at the end, i get it just like that and i can use it now, thank youuuuu so much….make my work much easier…i use it for my simple accounting job, its been very helpful..

Chase K.No Gravatar April 13, 2015 at 1:26 pm

Great explanation. Very clear, thank you much. One very minor detail – you have an extra open parenthesis in your equation in the Remembering It All section. It is before the MATCH function:

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

Silas AlexanderNo Gravatar April 14, 2015 at 5:11 am

Thanks a lot! The perfect explanation.

MonteryNo Gravatar April 15, 2015 at 5:29 pm

Gotta say thanks! I really like the method you use to remember the formulas for Index/Match. Just hope I remember it next time I need it! :)

CONNIENo Gravatar April 18, 2015 at 4:33 pm

Please see sample below, column A with correspondence price on B. I want to pull the prices to column D when the series number match on column C and A.
Can I use formula as: =INDEX(B2:B6),MATCH(C2,A2:A6,0)), it doesn’t work, and there are more than 800 series numbers on column A and C (C even has 1000 series). When I copy the formula down from D2 to D3, the arry in formula keep adding. appreciated your help.
231 $10.20 128 ???
280 $4.35 327 ???
041 $6.72 231 ???
327 $15.13 280 ???
187 $45.90 041 ???

andrewNo Gravatar May 1, 2015 at 9:01 pm

I have a 55 cells in a row array on line 17 that contain either a value of 1 or 0.
I want to find out which is the last cell on line17 that contains the number “1” and then find and give me the associated date text attached to that cell column from Row15.

RodrigoNo Gravatar May 6, 2015 at 2:46 pm

great job! thanks!

ShariqNo Gravatar May 11, 2015 at 10:58 pm

Nice way to explain a tricky formula, since a long time I have been trying to get a hold of this formula. and the last statement “=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))” is really awesome, it helped me to retain the formula for future purpose.

NuelNo Gravatar May 19, 2015 at 8:24 am

please i have two different tables with staff id’s and the second table contains both staff staff id and account number, my intent is to pull the exact match from the source table to a blank column to pull the exact account number that matches the staff id’s, how do i go about this, i have been finding it difficult to resolve

SamanthaNo Gravatar May 21, 2015 at 4:36 am

For Index Match Functions, is it possible for Index to be nested into Match?

JuanitaNo Gravatar May 23, 2015 at 12:07 pm

Perfect to comprehend, the best explanation on the web. Excellent work.

MaxNo Gravatar May 26, 2015 at 2:29 am

Explanation = BEST
You have made me fall in love with V-LOOKUP and MATCH INDEX.
Excellent job. Keep it up.

LucasNo Gravatar June 7, 2015 at 6:04 am

I have been using =IFERROR(INDEX($F$13:$F$1353, MATCH(0,COUNTIF($X$12:X12, $F$13:$F$1353), 0)),””) and it work perfectly in order to bring unique values from a column with repeated values. However I use a filter on the repeated values column “F” and when I deselect any item from the “F” filtered column I would like to have this item removed from the unique items list created. Couldn’t figure out a formula to make this work.

Thanks in advance!

Excel DummieNo Gravatar June 10, 2015 at 7:37 am

Hi all,
How do you make a screenshot with the A,B,C, etc. at the top and the 1,2,3, etc. at left side of the picture? Because now when I make a screenshot I can only make one of the fields in the Excel sheet….

Excel Dummie

sajeerNo Gravatar June 15, 2015 at 6:58 pm

i have one doubt how can i change the sheet name using formula wise an also i need to use the cell name as sheet name one more thing i am ready to change the cell name name only not sheet name so please give me a good solution to clear this issue as soon as possible

hmmzzhNo Gravatar June 18, 2015 at 5:39 am

Good Job Mark!

JenniferNo Gravatar June 22, 2015 at 4:33 pm

THANK you!!!! This really helped me at work today.

AngelaNo Gravatar June 24, 2015 at 7:08 pm

Hi there, has anyone used Index/Match in tandem to return a third value.
What I’m dealing with is two spreadsheets, one has about 4000 policy numbers in it, the second also has about 2500 policy numbers that I want to compare and match and then return the another 3rd value in the next column (a numeric key)? Thanks, in over my head on this one!

AllanaNo Gravatar June 26, 2015 at 12:14 am

I can not get this to work for me. I am trying to take a spreadsheet and create a customer view. I would like to type into Cell B3 the id number and return in cell F3 the information that correlates to that client numbers bday. So I have all the information in multiple cells. Starting at line 36 the the info appears in columns across.

This I wrote the following =INDEX(C36:C4000,MATCH(B3,F36:F4000,0))

Where I thought I was telling it to index everything in Column C from line 36 down and then once it finds the client number I typed into B3 return the corresponding information from that row in column F.

What am I doing wrong?

Yang WangNo Gravatar June 26, 2015 at 2:03 am

Thanks a lot. The explanation is so grateful for me!

Mark WongNo Gravatar June 29, 2015 at 4:24 am

Thanks for the catch. Have updated it to the proper format.

Mark WongNo Gravatar June 29, 2015 at 6:12 am

Hi Leigh,

I think you might have flipped the syntax. Please check out this recent post regarding the most common INDEX MATCH errors. Let me know if you still have issues.

TomasNo Gravatar July 10, 2015 at 12:55 pm

Awesome explanation!

I’ve successfully made calculations that I needed after looking into your instructions only once!

Actually, now I feel I completely understand the methodology how INDEX/MATCH formulas combination works.

Thank a lot!

fredNo Gravatar July 11, 2015 at 6:44 pm

Hello mark-
i am just starting with excel programming and I read with interest your notes about
index match being better than vlookup
I have had much experience in programming with dBase 4 ver 2 (still using DOS)
Modern hardware forces me to switch to excel – so the learning curve starts over again.
In my database programming i always develop a unique reference for each record.
It strikes me that when using index match i may benefit by always putting the unique reference in column “A” and using this as a search reference.
what do you think??

fred from south africa

MaxNo Gravatar July 16, 2015 at 3:11 am

Excellent examples on how to use VLOOKUP and INDEX MATCH.

Keep it up!

NetraNo Gravatar July 17, 2015 at 9:29 pm

Excellent explanation. Very useful and powerful function.

Thank you.

ShaktiNo Gravatar July 20, 2015 at 2:09 pm

Very well explained. I found this article very useful.. Thanks a lot for your inputs.

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

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.

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.

Leave a Comment

{ 25 trackbacks }

Previous post:

Next post: