**The Complete Guide to INDEX & MATCH**

# INDEX & MATCH for Flexible Lookups

# Contents

**CHAPTER 1**

## The Basics of INDEX & MATCH

**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:

- 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:

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:

- 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*

## 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.

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

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.

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.

## 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.

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

**Excel Dashboards that Inform & Impress**

Excel Reverse Matrix Lookup |July 21, 2017 at 4:37 pm[…] 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 […]

HariharanJune 25, 2018 at 7:58 amVery informative. Enjoyed learning the syntax of Index and Match and how when they are put together can lead to awesome lookups…….

FrancisAugust 29, 2018 at 6:54 amHi

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.

bobSeptember 27, 2018 at 8:22 pmcomment share and subscribe

Naresh RNovember 8, 2018 at 2:20 amwhere is the free e book

Leila GharaniNovember 15, 2018 at 1:57 pmHi Naresh – you’ll get the link once you sign-up.

jeffrey agostaNovember 30, 2018 at 2:41 pmLeila –

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 GharaniDecember 2, 2018 at 9:04 amYou’re welcome. I haven’t come across such a restriction. How many rows / columns of data?

Ajit KumarDecember 26, 2018 at 2:56 amI 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 GharaniJanuary 3, 2019 at 12:03 pmHi 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.

RobJanuary 3, 2019 at 6:30 amLove 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!

M. W.January 30, 2019 at 10:53 amHi 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 GharaniFebruary 9, 2019 at 6:03 pmHi – 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.