[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Excel: Using INDEX and MATCH

Published on
10,916 Points
5 Endorsements
Last Modified:
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directional lookup, adding an additional level of power to the formula.

How to use it:-
=INDEX([Index Array], [Optional Row to return], [Optional - Col to return])
=MATCH([Lookup value], [Lookup Array], 0)

(For the purposes of this article I am omitting the Area argument from index and using EXACT match type)

Index array - The range of values you want to return information from.
Row to return - Row number, within the array, to return the value from. If omitted row 1 will be used.
Col to return - Optional. The column, within the array, to return the value from. If omitted column 1 will be used.
Lookup Value - Value to match against.
Lookup Array - Range of values to match against

You use the MATCH formula to get your row, column or both. This will then get the appropriate data from the INDEX array.

I have attached an example spreadsheet. If you change the values in "V Value" and "H Value" you will see how it affects the result.

Why to use it:-
Because you don't have to sort the data within the range you want to search it is very useful for data that will grow dynamically. It also allow you to treat data as a true array within a single formula. So you can perform bi-directional lookups and store data points within different data ranges but with the same reference.

This could be extremely powerful if you need to store references to external source of data, for example. You have a table with the columns "Customer", "External Ref 1" & "External Ref 2". You can then have a summary sheet where the user can input the customer and without having to go searching and without the need for macros you can give the relevant external information.

Also because of this is two separate formula if you need to return several data elements the MATCH can be put into a separate cell. This reduces the number of calculation required when compaired to using V or HLOOKUP

Featured Post

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month