• open dock
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us
Change this Limited
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us

Why INDEX-MATCH is better than VLOOKUP

There are many ways of looking up and retrieving values from a table of data in Excel. The VLOOKUP() and HLOOKUP() functions are widely used because of their simplicity, but they have some limitations and can fall down in certain situations. The INDEX(MATCH()) combo is a far more robust and reliable method. This blog will detail how to use the formula to best effect.

This blog will assume you already know how to use the VLOOKUP function. You will therefore know that the following limitations can sometimes be a source of frustration:

  • You cannot look up values to the left of the reference. VLOOKUP() only looks to the right.
  • You must specify a single column reference (e.g. 5th column to the right) where the desired data must be located.
  • You can only specify a single lookup value as a reference.

The INDEX() and MATCH() functions both have uses in their own right, but it is when they are paired together that they yield the greatest benefit.

On its own, INDEX() comprises a minimum of two parts:

  • An array (i.e. column(s) of data where the target value can be found;
  • A row reference;
  • A column reference (if more than one column is specified in the array)

In the example below, the formula is searching for data in columns B and C. It is pulling in the value that is on the 5th row of the array, and in the 2nd column.

01

On its own, MATCH() effectively performs the reverse function. It reads through a row or column of data, searching for a specified lookup value. It will then return the row / column number where the lookup value is located.

In the example, below, we have specified the value in cell I4 (i.e. “USB Cable”) as the lookup value, and column C as the array. It has returned a value of ‘5’ because USB Cable is on row 5 of the table.

02

The MATCH() function is therefore acting as our lookup, returning a column and row reference, which then allows the INDEX() function to pull out the relevant data.

An example of the two functions working in tandem is shown below. The product “USB Cable” is our lookup value. The three cells in yellow all contain INDEX(MATCH()) formulas.

03

You can see that the MATCH() part of the formula references a lookup in column C, and the INDEX() part of the formula then finds a value from column B, which is to the left of the lookup. This would not be possible with a VLOOKUP() formula.

In each of these formulas, the INDEX() element refers to just one column, hence only one MATCH() expression is required. If the INDEX() refers to multiple columns, a second MATCH() expression is required to provide a column reference. This can be referenced against the column header, as below:

04

This formula does not contain any pre-defined row or column references. Everything is based on lookup values, which means that even if rows or columns are inserted into the table, the formula will still find the required data.

The final trick that INDEX(MATCH()) allows you to do is to interrogate an array based on multiple lookup criteria. For example, in our sample data set we might want to find the details for the product “Keyboard”, but this appears in the list twice.

We therefore need to specify both a region and a product as lookup values. This can be done using the first MATCH() expression. Two lookup values can be set, separated by an ampersand (&). The two lookup ranges would also be separated by an ampersand, as shown below.

Note: This formula must be entered as an array (CTRL, SHIFT + ENTER).

05

Download The Workbook

Index Match (Excel) workbook

Formula Breakdown

The standard (and most useful) INDEX(MATCH()) combo formula is shown below. It calculates using the following steps:-

INDEX() Element                 

=INDEX($B$3:$E$14,

Defines the data range where the target value can be found

First MATCH() Element

MATCH($I$14,$C$3:$C$14,0),

Find the value in cell I14 in the defined range and return the row number

Second MATCH() Element

MATCH(H$13,$B$2:$E$2,0))

Find the value in cell H13 in the defined range and return the column number

Get The Formula

The full formula used in this blog is as follows:-

=INDEX($B$3:$E$14,MATCH($I$14,$C$3:$C$14,0),MATCH(H$13,$B$2:$E$2,0))
  • 24 Jun
  • Tobias Owen
  • Excel
  •  3 Comments
  •  Like
  • Formula , INDEX-MATCH , VLOOKUP()

Share This

Related Posts

  • Calculate Time Elapsed using NETWORKDAYS()
  • Rank by Multiple Criteria in Excel
  • Tax Year Excel Formula
  • Rock Paper Scissors
Calculate VAT in Excel →← Merry Christmas
1

Comment

  1. VINEETH P
    8th July 2018 at 9:34 am

    VERY HELPFUL. THANK YOU SO MUCH

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Recent Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • Archives

    • December 2018
    • November 2017
    • June 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • September 2016
    • August 2016
    • July 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • September 2015
    • June 2015
    • May 2015
    • April 2015
  • Categories

    • Case Studies
    • Excel
    • Insight
    • News
    • Portfolio
    • Power BI
    • SAP Dashboards
    • SharePoint
  • Search our site

  • Social

  • Recent Blog Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • © 2016 Change this Limited
    Website by Change this
    Standard Terms of Business
    Privacy Policy

    Delivering the difference

This website uses some cookies to improve your experience within the website.


We also use cookies to understand how visitors use our website Accept


To find out what cookies we use and for more information, Read More.
Privacy & Cookies Policy

Necessary Always Enabled

Non-necessary