How to Use INDEX MATCH

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

{ 42 comments… read them below or add one }

PrateekNo Gravatar February 16, 2016 at 1:17 am

What a great article, thanks so much for the info, it was really helpful.

jonNo Gravatar February 18, 2016 at 6:51 am

I’ve tried to learn index + match from other sites, but only when i got to your site did i actually understand it. Thank you very much for this excellent tutorial!

TexasscotNo Gravatar February 26, 2016 at 2:59 am

Agree with others. Best explanation of this I’ve seen. 10/10 :)

KateNo Gravatar March 4, 2016 at 10:17 am

brilliant explanation. Thanks

SachithNo Gravatar March 17, 2016 at 5:15 am

Thanks for the knowledge sharing. This was very valuable and you have explained in a simple way.

jilanNo Gravatar March 31, 2016 at 11:43 am

thank you keepppppppppp writting

AmandaNo Gravatar March 31, 2016 at 6:50 pm

I am trying to figure out how to reference a specific value from a cell that specifies a formula.
Let’s say I have a total of 6 data points, but only use 4 of them to make the curve depending on if I want the lowest or highest values. I want to be able to have a separate cell that spits out the value of the lowest data point used for the slope calibration.

For example my x-values are in A1-6, and the y-values are in B1-6
so in my first cell I have the formula “=slope(B1:4, A1:4)” so that I am only calculating the slope based on the 4 lowest data points.

I want a second cell that references the lowest y-value used based on the range specified in the slope formula.So if my slope is “=slope(B1:4, A1:4)” the result will be the value in B1, but if I change my slope to be “=slope(B3:6, A3:6)” the result will be the value in B3.

Hopefully that makes sense. Please help!

vicsarNo Gravatar April 15, 2016 at 5:59 pm

Ok. Extremely useful, thank you. This will replace VLookup for me.

kunal kumarNo Gravatar April 22, 2016 at 9:53 am

HI
it’s really amazing example of index and match function and also easy to understand anybody thanks for this………….

PINKYBNo Gravatar April 28, 2016 at 1:20 pm

hi..

i need a help here. Can index match be used to search multiple data in multiple sheets?
if so, you please explain.

VinodVNo Gravatar April 30, 2016 at 9:17 am

Index match made easy. Can u explain the same for multiple criterias

SandyNo Gravatar May 1, 2016 at 2:10 pm

AWESOME :-)
Thanks a million

danitaNo Gravatar May 4, 2016 at 2:30 pm

Thank you, this was very helpful!

Cany CNo Gravatar May 5, 2016 at 1:12 pm

I will never look back at Vlookup though i will miss it. index match is the real deal

gamingbroNo Gravatar May 5, 2016 at 6:37 pm

wow, this was a finely explained & written article. Thank you!

somashekar SNo Gravatar May 12, 2016 at 10:49 am

Its really amazing thanks lot

DorotaNo Gravatar May 20, 2016 at 11:39 am

The easiest explanation ever. I was really put off using this function however it’s so easy after your explanation. Many thanks for that.

kenNo Gravatar May 24, 2016 at 4:20 pm

Great and simple explanation! Well done!

MIANo Gravatar May 31, 2016 at 4:22 am

THANKS :)

PJNo Gravatar June 15, 2016 at 6:10 pm

Thank you. This is very helpful

RahulNo Gravatar June 27, 2016 at 6:28 am

Thanks a lot

ShawnVWNo Gravatar June 27, 2016 at 6:39 pm

A trick I use to “remember it all”: First, enter just the MATCH formula. When you get a column of numbers, you’ll know you did it correctly. Then, go into the cell and insert the INDEX formula; the “Column I want a return value from” will be first, then the Match formula you already entered.)

Or, if you like, enter the Match formula in one column, and the Index formula in the column next to it, referring to the Match column.

JoshNo Gravatar July 5, 2016 at 6:08 pm

Saves me a ton of time! Replacing the binary vlookup with this function now.

Thanks!

RichardNo Gravatar July 6, 2016 at 7:26 pm

Nice clear explanation. Thank you!

AmandaNo Gravatar July 12, 2016 at 10:51 am

Thank you for describing the values in plain English – that was SO useful to me. I spend a lot of time thinking ‘hang on, what is an array? I thought the table was an array!’ and ‘wait, which one is the lookup value again?!’ I’ve successfully looked up some relevant payment codes.

Dumitru DANILANo Gravatar July 21, 2016 at 6:26 am

Hello,

I need help to find a excel formula, which can return the value “Jx” for any interrogation “Py & Dx”.

Bellow you will find the global table, where are all input data (is an manual table).
Table J1 J2 J3 J4 J5
P1 D1 D2 D3 D4 D5
P2 D5 D1 D2 D3 D4
P3 D4 D5 D1 D2 D3
P4 D3 D4 D5 D1 D2
P5 D2 D3 D4 D5 D1

The formula that I need, is to return me the values “J” for the project “P” at date “D”.
Test 1 D1 D2 D3 D4 D5
P3 J3 J4 J5 J1 J2

Test 2 D1 D2 D3 D4 D5
P1 J1 J2 J3 J4 J5

General (schematically) form of my question:
B
A C

C
A ??? Formula to return B

Thank you in advance for your help!!!

Have a nice day!!!

LeenaNo Gravatar July 22, 2016 at 7:45 pm

Excellent Tutorial. Thank you.

CamilleNo Gravatar July 22, 2016 at 9:32 pm

I looked at multiple articles on this topic and was still confused until now! Thanks!

AidanNo Gravatar July 24, 2016 at 10:49 pm

Thank you!

Mohammed YasserNo Gravatar July 25, 2016 at 9:10 am

I fully agree with the others. This is the best explanation I could find anywhere on the Web on how the INDEX and MATCH functions could be used as an alternative to VLOOKUP. Because you’ve used simple and small data sets, it’s really easy to figure out which cells are involved and what the formulas are working on.

Thanks a lot for this…I’ve saved this page in my browser favorites at work in case I happen to forget how to use the formulas.

TU GIA BAO LONGNo Gravatar July 28, 2016 at 8:40 am

It’s help me to improve my Excel skills. Thank you for your information.

BharatNo Gravatar July 28, 2016 at 3:15 pm

I have gone through some other sites but i didnt understand logic behind the match, now i understand cleartly with perfect example, thanks a lot keep posting new stuff with simple and easy examples.. and i have another question at the same time i need to pick the latest date with multiple columns all at a time by doing this Index and Match… can you post with and example.

waiting for your post ..

SohanNo Gravatar August 9, 2016 at 9:26 pm

Hi Guys,

i have below data with me, i need one formula that returns row value in single formula.

A B C D E
Nakul 100 120 130 140
Sohan 240 251 273 249
Sharad 359 354 341 371

For Example
100 Nakul
120 Nakul
130 Nakul
140 Nakul
240 Sohan
251 Sohan
273 Sohan
249 Sohan
359 Sharad
354 Sharad
341 Sharad
371 Sharad

Can you guys please help

SohanNo Gravatar August 11, 2016 at 9:13 pm

Can you guys please help

RogerNo Gravatar August 16, 2016 at 1:48 pm

What happens if there is more than one 0 in column A with my function it just returns the same value

VipinNo Gravatar August 19, 2016 at 5:59 am

Very nicely explained, thank you so much .

bhanuNo Gravatar August 27, 2016 at 1:11 pm

its really good

AfzalNo Gravatar August 28, 2016 at 7:33 pm

Simple explanation , great

ZoricaNo Gravatar September 6, 2016 at 10:44 am

Thank you a million! This works also with multiple sheets unlike the vlookup or hlookup! Thanks!!

BryanNo Gravatar September 8, 2016 at 1:55 pm

Don’t like #N/A or 0 all over your sheets when the reference is not available or the result is a blank cell? Want to be able to drag or copy your formula all over the page? Try this noting where the $ are and where they are not. =IF(IFERROR(INDEX(Sheet3!D$2:D$380,MATCH(‘CFI LIST’!$D6,Sheet3!$B$2:$B$380,0)),””)=0,””,IFERROR(INDEX(Sheet3!D$2:D$380,MATCH(‘CFI LIST’!$D6,Sheet3!$B$2:$B$380,0)),””)) The IFERROR gets rid of the #N/A result and the IF gets rid of the 0’s.

SongNo Gravatar September 13, 2016 at 5:50 am

Thanks for your explanation. I agreed so many website have terrible explanation.

Mohamed MaherNo Gravatar September 14, 2016 at 7:15 pm

Man, Your way is just great , thanks a lot dude:)

Leave a Comment

{ 3 trackbacks }

Previous post:

Next post: