The Complete Guide to INDEX & MATCH

INDEX & MATCH for Flexible Lookups

CHAPTER 1:

The Basics of INDEX & MATCH

Excel experts generally substitute VLOOKUP with INDEX and MATCH.

Here’s why:

Unlike VLOOKUP, which searches only to the right, INDEX and MATCH can look in both directions – left and right.

INDEX & MATCH can perform two-way lookups by both looking along the rows and along the columns to find the intersection within a matrix.

INDEX & MATCH is less prone to errors.

Assume you have a VLOOKUP where the final value you want returned is in column N.

Your lookup value is in column A.

You need to highlight the entire A to N range and then provide your index number as 14.

If you happen to delete any of the in-between columns, you would have to update that index number.

You don’t need to worry about this when you use INDEX & MATCH.

All in all, INDEX and MATCH is more flexible than VLOOKUP.

What it is

INDEX and MATCH are “nested” functions – two functions used together.

The first argument of INDEX is to give it an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to move to find the correct value. You cannot move outside the INDEX range.

The syntax of INDEX is:

 index and match, excel, dashboards

  • The range where the return value resides.
  • Number of rows to move down (the row index)
  • Number of columns to move to the right (the column Index). This argument is optional. If you only have one column, you can leave the column argument empty, otherwise, you need to specify the number of columns to move over, in the range.

Here is an example of INDEX without MATCH:

index and match, excel, dashboards

As shown above, INDEX alone is not useful for identifying the number of rows or columns to move. This is where MATCH comes into play. It helps the INDEX function to locate the answer.

The MATCH function was designed to return the position or address of the lookup value to the INDEX function. This is what differentiates MATCH with other functions. It does not return the VALUE in the cell but returns the POSITION of the cell within a specified range.

The MATCH function’s syntax is as follows:

index and match, excel, dashboards

  • Like VLOOKUP, Match needs a lookup (target) value. The user can reference a cell or directly type the value into the formula.
  • Lookup_array: The “list” (range) where the return values are located.
  • Match_type: 0 for an “exact”

In the below example, the MATCH function returns the value of 4. 200 is 4 rows down from the specified range.

The important point to note on the MATCH function is that it’s a one-way street – you can only have one row or one column selected.

Tip: If your MATCH contains multiple criteria then you need to turn the multiple criteria into a one-way street. You can do this by wrapping the MATCH function inside another INDEX or by converting your formula to an array – CSE – function. To watch this in action, refer to the INDEX & MATCH Lecture in my Advanced Excel Training XelPlus.com/AdvancedExcelCourse

index and match, excel, dashboards

Putting it together

When INDEX and MATCH are used together, the MATCH function finds the look up value’s row / column index and then hands this value off to the INDEX function to get the lookup value. The example below shows how these work in tandem.

index and match, excel, dashboards

This formula can be further manipulated to find the entity with the highest sales. To do this, simply nest the MAX() function inside the MATCHfunction to find the location of the highest number.

index and match, excel, dashboards

The VLOOKUP function has to include all the columns in your range even if in-between columns aren’t needed. INDEX and MATCH only requires the return value column which can significantly reduce processing time in larger worksheets.

The easy way to remember the syntax of the INDEX and MATCH is

=INDEX (The range location of my answer, MATCH(The value I’m looking for, The look-up range to find the Value, 0))

Matrix Lookup

Use INDEX MATCH & a second MATCH in cases where you need to look up a value in a matrix. MATCH is used twice: once for the rows and once for the columns. In this example, the return range in the Index function involves multiple columns and the MATCH function instructs the formula to move down a certain number of rows and move over a number of columns across the sheet to retrieve the desired value.

Instead of just selecting a single row or column you need to index the entire table with multiple rows and columns as your array since your value is somewhere inside this matrix.

index and match, excel, dashboards

The syntax of the INDEX MATCH MATCH function is:

=INDEX(The full range where your answer can be found in, MATCH(The value I’m looking for in the rows, The range I need to find the Value, 0), MATCH(The value I’m looking for in the columns, The range I need to find the Value, 0))

Building on the INDEX and MATCH Function

By nesting INDEX and MATCH in other formulas you can create more complex, dynamic calculations. The example below, shows how you can nest INDEX and MATCH in the SUMIFS function.  This way you can show the SUM of either the sales column or the volume column depending on whether you select “Volume” or “Sales” in G4.  The summing value criteria it uses are West_01 in column A, 2014 in B and Jan in C. Once you change the value in G4 from “Volume” to “Sales”, the results are changed to SUM the values from the sales column. This can be used in dynamic dashboard reports.

index and match, excel, dashboards

Video and Workbook

INDEX & MATCH is one of my favourite functions because it can be used in many complex situations. A complete understanding of this function is what separates the gurus from the merely capable.

Feel free to Download the Workbook HERE.

Excel Download Practice file

Check out Part 2 of this guide, where we go into more advanced uses of INDEX & MATCH.

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

23 Comments

  1. Excel Reverse Matrix Lookup | July 21, 2017 at 4:37 pm - Reply

    […] you’re familiar with Index and Match you know how to solve a matrix lookup problem. You want to find a value inside the matrix by […]

  2. Hariharan June 25, 2018 at 7:58 am - Reply

    Very informative. Enjoyed learning the syntax of Index and Match and how when they are put together can lead to awesome lookups…….

  3. Francis August 29, 2018 at 6:54 am - Reply

    Hi

    Need Help in this. I have a sheet for invoice and another sheet with rates for few sku’s.

    when i enter 2 columns in sheet one with some text and values i want a formula to look into the second sheet for the text entered in the 2 columns and get the value from the sixth column in the first sheet.

  4. bob September 27, 2018 at 8:22 pm - Reply

    comment share and subscribe

  5. Naresh R November 8, 2018 at 2:20 am - Reply

    where is the free e book

    • Leila Gharani November 15, 2018 at 1:57 pm - Reply

      Hi Naresh – you’ll get the link once you sign-up.

  6. jeffrey agosta November 30, 2018 at 2:41 pm - Reply

    Leila –
    Index(match)… Great stuff! Thanks for sharing. I use it a lot, but have found where the amount of data to search appears to break the functionality. Can you confirm this?

    Tku,
    Jeff A

    • Leila Gharani December 2, 2018 at 9:04 am - Reply

      You’re welcome. I haven’t come across such a restriction. How many rows / columns of data?

  7. Ajit Kumar December 26, 2018 at 2:56 am - Reply

    I want to know when the length of the Index Array & Lookup array is not same then Index/Match formula will properly. If not is there any method to avoid such error.

    • Leila Gharani January 3, 2019 at 12:03 pm - Reply

      Hi Ajit – the length of the INDEX and MATCH area has to be the same, otherwise MATCH will return the wrong address to INDEX. The correct formula in your case, will really depend on the setup and what you’re looking for.

  8. Rob January 3, 2019 at 6:30 am - Reply

    Love your work Leila, I have watched a lot of your video tutorials!

    Any chance you can do one more detailed video on index / match / offset / sumproduct solutions looking at the subtleties of summarising data for specific date ranges (eg previous 12 months where there are 5 years of data in columns by month)? Especially with tables/structured references, I find there are a few ways to do things but is difficult to do well…

    Thanks again!

  9. M. W. January 30, 2019 at 10:53 am - Reply

    Hi Leila, Hi Everyone,

    Thanks for sharing all this great stuff!

    I have a bit of a complex lookup to do: Say I have a list of unique IDs in Col1 and the information I am looking for is either in Col. 3 or Col. 4 depending on the item. (Col. 3 & Col. 4 data is distinct, no replicates). I also have a list all all the lookup values I am interested in (some from Col. 3, some from Col. 4). Is there a way to use index(match()) to lookup the information ?

    Many thanks again!

    • Leila Gharani February 9, 2019 at 6:03 pm - Reply

      Hi – if you have the logic somewhere on which column to lookup the value in, you can make it work. Somewhere you can have a table with the lookup values and which column you need – you could perform a lookup on that table, get the column and use that in your INDEX formula. Or you could use helper columns. Write two formulas – one that gets the value of Col 3 and the other Col 4 – and then in a final column figure out the one you want to show based on the master table. You can then hide the two helper columns.

  10. Col Delane February 25, 2019 at 10:55 am - Reply

    Contrary to the statements at the very top of this post as to why some so-called Excel experts generally substitute VLOOKUP with INDEX and MATCH, it is possible (and relatively easy) to have VLOOKUP return a value from a column to the left of the lookup column!

    Assume the following:
    • lookup values are in D1:D5
    • corresponding return values are in A1:A5
    • VLOOKUP formula is in A10
    • lookup value is in D10

    The following formula in A10 will return the matching value from A1:A5 for the lookup value in D10:

    = VLOOKUP( D10, CHOOSE( {1,2}, $D$1:$D$5, $A$1:$A$5 ), 2, False )

    As you can see, the CHOOSE function with its array argument provides the magic switch. This VLOOKUP/CHOOSE formula behaves much like an INDEX/MATCH combo, and also means that only the lookup column and return value column need to be referenced no matter where in the sheet they are located. This also avoids the other issues where you happen to insert or delete a column in what would normally be within the range specified as the Table_array argument where you have used a constant as the index number (the latter is not a good practice in any event, as MATCH or COLUMNS should be used to make the Index number a dynamic argument.)

    Comparing a naked VLOOKUP (one function) with an INDEX/MATCH (two functions) is simply unfair!

    • Bryon Smedley April 7, 2019 at 3:06 pm - Reply

      This is one of my most beloved tricks when using VLOOKUP; using the CHOOSE function to allow leftward lookup. It’s great for rebuilding the table in memory or creating a table of just the needed columns instead of scanning tables with dozens or hundreds of columns. Be aware that at some point, tables with hundreds of thousands of rows may consume more memory than some systems can handle. In those cases, INDEX/MATCH may be the better course of action.

      Two thumbs-up for creativity!!!

  11. Aida March 15, 2019 at 8:44 am - Reply

    Hi Leila ….

    I’m new to excel formula. I have this formula array :

    =IFERROR(INDEX($AL$10:$AL$500, SMALL(IF($E$2=$F$10:$F$500, ROW($F$10:$F$500)-MIN(ROW($F$10:$F$500))+1, “”), COLUMN(A1))),”-“)

    This formula extract data from AL10:AL500 base on the criteria that i enter at E2 that refer to a set of data in F10:F500.

    The problem is that the result appeared include a duplicate value as well.

    May i know if there is any solution if i want the formula to produce only unique result?

    Hope my question are clear enough for u to answer.

    Thanks in advance

    • Bryon Smedley April 7, 2019 at 8:07 pm - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      XelPlus Team

  12. C. P. March 27, 2019 at 2:35 pm - Reply

    is there a way of using the index and match formula together in a way that you return not only the value in a cell, but the entire row associated with it? I have a database where all the information I’m looking to index is in one column, but I want to return the entire row of each result I find.

    • Bryon Smedley April 7, 2019 at 7:44 pm - Reply

      You will be able to perform this action once Dynamic Arrays are released. Dynamic Arrays are in their final phase of development and should be out soon.

      Thanks – XelPlus Team

  13. James April 12, 2019 at 1:31 pm - Reply

    I am using a complex Index-Match and found the result in column 10 (10 over), then I needed to add a column in the table. My result (10) is now wrong. How do I fix this without rewriting all of the formulas?

    • Bryon Smedley May 5, 2019 at 11:19 am - Reply

      Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

      The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

      Microsoft Excel Tech Community

      With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

      Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

      The XelPlus Team

  14. cheap now reseach papers at now April 24, 2019 at 11:54 am - Reply

    Thanks to the shared diagrams and tutorials, the entire process is maximally simplified. I’ll try to follow your guidelines.

  15. KAUSHIK MAULIK May 25, 2019 at 2:13 am - Reply

    Brilliant

Leave A Comment

Share This