Excel Formula: Index Match

A common task when working in Microsoft Excel, or any other spreadsheet tool, is joining data from separate tables or dataframes. The most common, and well-known, method for accomplishing this, is the VLOOKUP() formula. Shown below is an example that is using VLOOKUP() to help create a revenue calculation table from two tables containing Products and Orders.

Using VLOOKUP() to retrieve SKU from the Orders table, and join it to the appropriate record by matching Order number.

The VLOOKUP() formula is fairly simple and easy to use, but it has two obvious shortfalls. The first is that the ID column must be on the left-hand side of the table you are retrieving data from, this is not the case for the Products table above. The second issue is that you must count the number of the column you wish to retrieve, this is easy enough in the example above where the column is #2, but in large tables this can be tiresome.

An alternative to VLOOKUP() is the combination of INDEX() and MATCH(), The INDEX() formula provides the column to be retrieved, and the MATCH() formula provides the row number, removing the need to count columns.

INDEX() and MATCH() used to retrieve order quantity.

Finally we see the ability of INDEX() and MATCH() to work with an ID that is not on the left-hand side of the table,

INDEX() and MATCH() to retrieve price.

Now, with the help of INDEX() and MATCH(), we have assembled all of the building blocks of our Revenue table. The addition of a simple formula to multiply Quantity and Price will yield the Revenue calculation.

Completed Revenue table, thanks to INDEX() and MATCH().

Leave a Reply