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

{ 191 comments… read them below or add one }

silviafiniNo Gravatar June 1, 2014 at 9:27 am

Wow, thanks a lot! Finally I can use it!

And I’d also like to try helping answering question since we share knowledge:

Jenni: “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.”

That happens because the reference moved when you sort the table.
You can simply block the whole column (A:A) instead of just few rows of the columns (A2:A18).

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

Yes that is possible. Just simply move to another excel sheet or workbook while you are typing the formula.

sunil Godbole: “I want to find out for last how many months customer have not given order (order value=0)”

You can use =COUNTIF([the whole row],0)

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

You can apply the formula explained above for that, as long as you have the list of the abbreviation code you want.

Ashley: “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..”

You can input the formula inside =IFERROR([your formula(index match or any)],”NOT MATCH”) or instead of NOT MATCH you can input anything that you want to replace the error value.

Bharat & Heather:
“but now I want to increment the output by 1 each time the input appeared.”

I’m not really sure what you expect, but if you want to increment you can simply add basic math in the formula. So your sample formula would be =INDEX($B$2:$B$69,MATCH(H26,$F$2:$F$69,0))+1

Naveen Kumar Singh: “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”

You can use =LARGE([salary column],2) for the second largest, and change the number 2 to 1 for the largest.

JRAJU: “if i have 4 columns of data, two columns combined entry, should look up values for the other two columns.
I want to know, which columns should be used for index, and which columns should we use match.”

It depends on what value you want to return. Column you use for index is always the column you want to return the value.

I hope it’d help. If you have any question you can email me to silviafini[at]gmail[dot]com
Maybe we can share knowledge about Excel, because I deal with Excel every minute of every day.

MKNo Gravatar June 12, 2014 at 9:48 pm

Superb.. One reading and I was sorted. Very nicely explained. This is the first time that I could properly understand index and match function

IrfanNo Gravatar June 16, 2014 at 5:20 pm

Good Explanation .

It will be highly appreciated , if you add more examples .

Joshua HamerNo Gravatar June 17, 2014 at 6:28 pm

Thanks so much for this tutorial, it’s incredibly helpful in the work that I do. However, I’ve run into an issue with it. I was able to get it to pull the data from one tab onto another properly, and I could use $ to lock the rows and columns to copy and paste it through the whole range. However, when I went to sort after doing this, the row references got completely out of sync and ruined the data table. Is there a way to lock the formula in place after a copy and paste to preserve the data without copying and pasting as values? Thanks!

Carl HermanNo Gravatar June 18, 2014 at 8:25 pm


What a great explanation!

I’ve read definitions of Index Match before and always been frustrated. Thanks for explaining it in a way that makes sense to me! :)

AsifNo Gravatar June 21, 2014 at 4:30 am

Your way of explanation is effective , good work.

KvlarNo Gravatar June 22, 2014 at 11:47 pm

I have a table 20×24500 cells. All info for each product is along one row. I want to enter the product code on another sheet, then I want it to return the product description (column 6) AND where it was made (column 5) in 2 separate cells, giving me 3 cells of data from just entering the product code.

CallumNo Gravatar June 23, 2014 at 1:40 am

You can use index,match function left OR right. All you gotta do is chuck in the offset function

JackNo Gravatar July 1, 2014 at 10:26 am


BaronNo Gravatar July 16, 2014 at 9:06 pm

This is absolutely perfect! Thank you so much for your simple explanations and useful screenshots. I understood your examples better than any other site.

RupeshNo Gravatar July 21, 2014 at 4:54 pm

Yes… Its really easy to understand. Thank You.

ManojNo Gravatar July 22, 2014 at 7:43 am

Is there any possibility to use two array??

content marketingNo Gravatar July 29, 2014 at 4:40 pm

Hmm it looks like your website ate my first comment (it was super long)
so I guess I’ll just sum it up what I wrote and say, I’m thoroughly enjoying your blog.

I too am an aspiring blog writer but I’m still new
to the whole thing. Do you have any recommendations for
newbie blog writers? I’d really appreciate it.

OscarNo Gravatar July 30, 2014 at 5:52 am

I’m using index-match to look up Unit Name by searching through Property Name first. I have 2 columns one with Property Name and another Unit Name. My questions is, there are 3 property names but more then 10 Unit names for each property. Whenever I use index-match it only looks up first property on the range and copies only first unite name next to that property name. I need excel to copy all units under that property when ever property one is selected. Can you help me with that please.


AnnNo Gravatar August 5, 2014 at 8:32 pm

Got it on my first try, thanks!

MrPNo Gravatar August 8, 2014 at 3:02 pm

Is it possible to use the INDEX MATCH combination when working with 4 variables? In the past I used the combination with two variables:


I entered field names to keep a clear overview of the formulas. The formula I made for 4 variables is this:


Without respect for the exact cells can anyone tell me whether this formula would work?

WoodyNo Gravatar September 2, 2014 at 2:42 pm

You can use index match on tables with multiple column and/or row keys.


Note the use of the ampersand to concatenate the fields in the MATCH function:


Rather obscurely, when working with arrays you must press {ctrl}{shift}{enter} rather than just {enter}. You can tell if this has been successful or not by if you can see braces {} around your function call (as in my index match example above).

Taiwo OjoNo Gravatar September 2, 2014 at 4:00 pm

WOW, very easy to understand, I needed to vlookup right to left, made it easy and I did it first time. nice

GaneshNo Gravatar September 4, 2014 at 5:41 pm

Can you please tell me how to handle the below scenario?

Apple 10 1-Sep-14
Orange 10 2-Sep-14
Banana 10 3-Sep-14
Apple 9 4-Sep-14
Apple 10 5-Sep-14
Orange 9 6-Sep-14
Banana 9 7-Sep-14

Now, I want to find the maximum in column B for Banana and get the Corresponding Column C value, which is 3-Sep-14.

I’m not able to get this value using Index and Match as it returns 1-Sep-14. Please let me know.

Darko LoncarNo Gravatar September 5, 2014 at 12:17 pm


i just tested using vlookup vs match+index and found this result : vlookup need 12 sec, and match+index need 24 sec to process 524.000 rows. I am using Excel 2013.
Formulas ; vlookup(a2;$p$4:$q$125;2;false)

FunkeNo Gravatar October 24, 2014 at 4:16 pm

I have some names downloaded from a database to lookup for in a manually inputed data.
The names are not arranged the same way what formular can i use to lookup for the names.

ChadNo Gravatar October 24, 2014 at 8:19 pm

I cannot get this to work with an ID column that contains numbers and letters, like a UUID. This only works for number only IDs.. Is there another solution, other than a full-blown VBA option?

SaxonNo Gravatar October 27, 2014 at 2:48 pm

This was extremely helpful, I always refer co-workers to this site for Index-Match clarification.

AnnaNo Gravatar October 28, 2014 at 4:09 pm

Thank you so much for this explanation! I’ve tried to understand Index Match a few times before but I was never able to wrap my head around understanding what it does.. Your explanation was extremely helpful..I finally actually understand the functionality for the formula! THANK YOU!

SUNIL AMITNo Gravatar November 5, 2014 at 11:30 am


RobynNo Gravatar November 6, 2014 at 9:36 pm

Thank you so much for this article. It was so easy to understand and allowed me to get my code working in just a few minutes as opposed to the hours I’d spent trying to figure out the same thing using other, less apt websites. Thanks for the thought you put into this.

BhagyashreeNo Gravatar November 7, 2014 at 3:32 am

Thank you thank you very much… :-)

AnthonyNo Gravatar November 13, 2014 at 4:50 am

Great Explanation ….. Thank you !

ElaNo Gravatar January 28, 2015 at 11:44 am

Really great explanation, I finally understood somehting that looked unattaiable to me before :)

IanNo Gravatar January 28, 2015 at 5:28 pm

This is the best explanation of Index,Match I have ever read. After reading your article I finally understand it. Thank you :)

PaulNo Gravatar February 2, 2015 at 1:09 pm

This is very interesting. However, I am struggling to use INDEX MATCH and have been using VLOOKUP and sorting the resultant table which is prone to error. This is what I want to do;
In Week 3, Lookup the value for Blackie in previous Week 2 which will be in col 6 or 7.
Return that value to Week 3 in either col 4 or 5 (ie; wherever value of ‘Blackie’ is).
Do this for all names in Week 3 in cols 2 and 3
I then do a calculation on the value returned in Week 3 (cols 4 and 5) and this results in a new value in col 6 and 7 in week 3.
I have to do this for all names in each week ( there are 52 weeks !) with the names being in either col 2 or 3 with their associated values in col 4 and 6 or 5 and 7.
So essentially I am building a running total in cols 6 & 7.
The only way I could think of doing it was to copy the values in col 6 and 7 to a table, sort the table and then use vlookup to get the corresponding value from Week 2 into Week 3

Could anyone help please ?

Col 1 2 3 4 5 6 7
Week 2 Arthur Belinda 1022 1039 1067 994
Bill Blackie 1028 972 1036 964
Eva Wally 1028 1002 1024 1006
Steve Tom 998 1019 994 1023
Westie Sunny 956 972 958 970
Hammy Bo 968 996 952 1012
Wiggy Charlie 961 1044 915 1090
Fred Manny 1004 1032 1000 1036
Neil Aston 968 1032 1012 988
CityBoy Liam 981 978 983 976
Week 3
Blackie Arthur 964 1067 942 1089
Belinda Fred 994 1000 984 1010
Charlie Steve 1090 994 1105 979
Manny Hammy 1036 952 1068 920
Tom Wiggy 1023 915 1007 931
Wally Neil 1006 1012 984 1034
Aston Eva 988 1024 978 1034
Bo Bill 1012 1036 1008 1040
Liam Westie 976 958 972 962
Sunny CityBoy 970 983 966 987

Excel resourcesNo Gravatar February 9, 2015 at 10:39 am

This is a great write up on how Index Match is a really useful tool in your Excel “tool box”. I recall an instance where I was using some Vlookups and having a real problem updating between 2 workbooks, a colleague then showed me how achieve this using Index Match. Great stuff Mark.
Regards, Craig.

MurtazaNo Gravatar February 11, 2015 at 11:18 am

Thank you so much for outstanding explanation. It was very well understandable, worked alot. :)

PreciousNo Gravatar February 18, 2015 at 8:12 am

hi there,

please advise on this scenario:
Activity Driver Cost object Jan feb mar apr………….etc
SAL stat BB 100 150 120 90
SAL stat COM 150 170 140 110
SAL stat COR 20 30 23 18
SAL stat WEA 10 12 11 6
SAL stat PUB 5 9 7 3

I need to be able to pull the months vertically what’s the best way of doing this?


PreciousNo Gravatar February 18, 2015 at 9:33 am

Please not to worry i have figured it out :)

Moataz EzatNo Gravatar February 22, 2015 at 9:46 am

I tested Index-Match for find the employee by 3 result “Cost center” but not sucssed .
you have another way .
from big data
Emp# Employee Plant # Plant CC# CC Name FV %
284 Ayman 2 الخط – 4 5500 PACKING DEPARTMENT 11 38
284 Ayman 1 الخط – 123 5500 PACKING DEPARTMENT 11 14
284 Ayman 3 الخط – 5 5500 PACKING DEPARTMENT 11 48


IrvinNo Gravatar February 25, 2015 at 8:33 pm

Hi, many thanks for the nicely explained tutorial. I have a list of all employees in column A and their line manager in Column B. Which formula I should use in column C to get line manager’s line manager? Is it really possible? Your quick response would be highly appreciated. Irvin.

Nice techniqueNo Gravatar March 1, 2015 at 5:37 am

Hi, can u say how to use index match in horizontal way for 20-40 rows

ErnestNo Gravatar March 1, 2015 at 5:08 pm

Excellent, I have seen some other sites explain this but you put it together quickly and made it easy to understand. Also that last “Idiots Guides” style explanation was the icing on the cake. Thank you for teaching this idiot :)

davidNo Gravatar March 5, 2015 at 2:00 pm


The best explanation ever. Now bookmarked and will return when I invariably forget how to use it the next time.

BrianNo Gravatar March 6, 2015 at 7:41 am

This is the perfect way to make all this understandable. Well done.

Leave a Comment

{ 17 trackbacks }

Previous post:

Next post: