Index Match

INDEX-MATCH is a close sibling of VLOOKUP and HLOOKUP with its ability to find a cell based on values found within specified ranges (or "arrays"), but is different in that it isn't a built in function, but the combination of two types of functions that allow us to achieve similar results. Why would someone deal with the complexity of Index Match rather than use VLOOKUP or HLOOKUP if the results are similar?

The answer is speed and flexibility. VLOOKUP and HLOOKUP are static search functions where you set a range to search and return a matching value based on the column or row number, where as INDEX-MATCH is dynamic in nature and can handle searches regardless of the directional structure of the selected cell arrays. How INDEX-MATCH is capable of this search capability is because it combines two search functions into one.

Before I explain how that is possible, let us dissect what each of these functions does on its own.

INDEX

INDEX is a spreadsheet function that takes a range of cells and then allow you to select a value in that range based on the numerical position of the row and/or column in the selected range.

Formula:

=INDEX(array, row_num, [column_num]) 
// [ column_name] denotes an optional value. We will not use this slot with INDEX-MATCH

MATCH

MATCH is a spreadsheet function searches for a specific value in a range of cells and then returns the relative position of that value in the given range.

Formula:

=MATCH(lookup_value, lookup_array, [match type]) 
// I recommend you always use “0” for the [match type] slot unless you have a unique scenario where there shouldn’t be an exact match

INDEX-MATCH

INDEX-MATCH is a combination of the previously described functions and will allow you to look up ranges based on the value found in other columns or rows.

First MATCH() looks up the row placement of the lookup value in the specified array.

=MATCH(lookup_value, lookup_array, [match type]) 
// Returns a row_number (e.g. 4)

Then the resulting row number is fed into INDEX() as the row number being looked up in the specified array.

=INDEX(array, row_number (MATCH Result)) 
// Returns a value in the format of that cell

When these two functions are used together, you will have a lot more dynamic search capabilities than both VLOOKUP and HLOOKUP and I will show you how that is possible with a function comparison.

INDEX-MATCH versus VLOOKUP & HLOOKUP

Let's say we have an e-commerce business with a database table that contains the items being sold, the price of those items and how many units are left. If we wanted to answer the question, “How many pant units are left?”, how would we do so with INDEX-MATCH, VLOOKUP AND HLOOKUP?

In a traditional database format like the one below, we would be able to answer this value with the VLOOKUP and INDEX-MATCH functions, but we wouldn’t be able to do such a thing with HLOOKUP because the first row in the cell range doesn’t contain the lookup value of “Pants”. VLOOKUP works because a row in the first column of our range matches our selected value and INDEX-MATCH works because we are able to MATCH “Pants” to a row in the first column and the row location of that value is passed as the row location for our “Units” range.

Points for VLOOKUP and INDEX-MATCH, but no points for HLOOKUP :(

If we had a table where there was a column per item rather than row, would we be able to use each function? No, we would be able to use INDEX-MATCH and HLOOKUP, but we wouldn’t be able to use VLOOKUP because there aren’t any rows in the first column that contain “Pants”. HLOOKUP works because the first row in the range has a value that matches our lookup value of “Pants” and INDEX-MATCH works for the exact reason as it did for the previous data set.

And this is an important point I want to hammer home. In both examples, only one of the two functions, VLOOKUP or HLOOKUP, worked, but INDEX-MATCH worked in both cases. This is because the direction and order does not affect INDEX-MATCH, making it much more flexible than either function regardless of the data set.

Still not sold? What if the data table had reordered columns, where the “Item” column was at the end of the data table? In this case, neither VLOOKUP or HLOOKUP would work, but INDEX-MATCH would!

VLOOKUP won’t work because the first column does not have a value that matches our lookup value.

HLOOKUP won’t work because the first row does not have value that matches our lookup value.

INDEX-MATCH does work because….do I need to say more :)

Hopefully by the end of this explanation you have a better grasp of INDEX-MATCH and feel comfortable enough to use this in place of other search functions. INDEX-MATCH is an extremely powerful tool for all analysts to have and as you have seen from my examples, VLOOKUP and HLOOKUP have their limitations.