How to Use INDEX MATCH

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

{ 19 comments… read them below or add one }

Sam SalveNo Gravatar May 27, 2017 at 8:18 pm

Excellent, index and match function it works,
and for above question.
To answer your question sohan.
its simple
Copy the numbers in front of the particular name ,and do
ALT+ESE
It works.

VishnuNo Gravatar May 28, 2017 at 8:02 am

Brilliant explain

BalvinderNo Gravatar June 6, 2017 at 5:01 pm

Great ! thanks of the knowledge

HeenaNo Gravatar June 9, 2017 at 7:01 am

Best way to explain

yygNo Gravatar June 26, 2017 at 4:16 pm

How to get the row number of the matched line, then? Thanks.

lakshmanNo Gravatar June 30, 2017 at 3:34 pm

Hi, thanks for the clarification on Index formula. I know almost all the usable formulas in excel but not Index formula. I was lazy understanding this formula and never ever missed this formula as i could achieve the required results through Vlookup etc., but today i learnt and understood based on your easy explanation or may be my brain is in good processing mode today (not sure :P) thanks for the same. I would use index from now on and especially after reading the advantages of the same. Thanks.

Melissa LotterNo Gravatar July 5, 2017 at 8:41 am

Great tutorial and explinations!

Thanks a bunch

ShawnNo Gravatar July 5, 2017 at 4:41 pm

Do you know if I can I use the AND or OR function with Index match? IF(AND(index(…match…..),(index(…match…)),

Thanks!
Shawn

tinaNo Gravatar July 10, 2017 at 8:58 pm

I don’t usually comment, this is spectacular! Thank you, you are phenomenal!!!

ANTHONYNo Gravatar July 25, 2017 at 6:54 pm

Thanks for your superb write-ups which are easy to understand.

I need your suggestion.

I am a Banker. Every Friday I extract from Bank’s system data of all my loan files. This file contains around 60 columns and around 6000+ rows. Each row pertains to unique account_id. Each row has balances, addresses etc.

Now I want to compare the latest data with say data of say 31st March to analyse which accounts have more balances and which accounts have reduced balances. I want this information for all the 6000+ rows. Now my question is how to use excel to get results quickly.

Thanks and regards
.

SCNo Gravatar August 1, 2017 at 4:21 pm

Thank you so much. This was the 3rd site I’ve been to trying to learn how to get my index match formula to work and I finally got it.

JayNo Gravatar August 15, 2017 at 2:13 am

Spot on! A concise explanation. I tried Index Match for the first time and will not forget it again. Thanks for your time. Jay

NathalieNo Gravatar August 17, 2017 at 1:29 am

Great explanations! Well done and thanks!

Maurício CruzNo Gravatar August 17, 2017 at 1:28 pm

nice… Very nicely explained.

MarkNo Gravatar September 7, 2017 at 6:55 pm

I gotta tell you, I’m using Excel for quite a few years now, I am by no means even close to an expert, but I do dabble with formulas, Pivot tables & Vlookups etc. The one thing I could never master was the Index Match, regardless of what I read or tried, That is, until I came across your website, you explained it so well that I got it in under 10 minutes.

Thanks a ton!

AndrewNo Gravatar September 23, 2017 at 2:37 am

Thank you for the clear explanation at then end. No bull, just a consistent way of doing lookups.

JethroNo Gravatar October 19, 2017 at 2:26 pm

Excellent, very easy to understand once its broken down and you break it down well!

TausifNo Gravatar October 27, 2017 at 11:04 am

Hi,

I deal with a large amount of data for budgeting and was thinking of whether there is a smart way of using an array formula to generate a list of variables from the data in a summary page to then use for sumifs, vlookups etc? Just want to ensure that any new items are automatically captured in the list rather than having to revise it.

LindaNo Gravatar October 31, 2017 at 2:15 pm

Thank you for the clear “Rembering It All”! It now makes sence to me!

Leave a Comment

Previous post:

Next post:

\n