Excel: Using INDEX and MATCH

Published on
10,802 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

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Join & Write a Comment

This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month