Up until the release of the XLOOKUP function, there has been significant debate in the Excel community around which function is better – VLOOKUP, or INDEX / MATCH.
But, what is INDEX / MATCH? This is a combination of 2 functions, the INDEX function, and the MATCH function. Together, they can provide some functionality that cannot be provided by VLOOKUP.
Why use INDEX / MATCH?
The most common use of the INDEX / MATCH function combination is to provide results that are to the left of the field that you are searching for.
Let’s say that you knew the account name, Cash, but you didn’t know what the account number was.
If you had the account number, and wanted to know the account name, you could use a VLOOKUP function to bring up the second column.
However; there is no way to look to the left; in fact Microsoft even says:
Tip: The secret to VLOOKUP is to organize your data so that the value you look up is to the left of the return value you want to find.
https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1
As a result, we need to use the INDEX / MATCH combination in order to get the results we want!
Explaining the 2 functions.
Since there are 2 functions in an INDEX / MATCH, each one will perform a seperate job in order to get the result that you are looking for.
The INDEX function
The INDEX function provides the value that is a specified number of rows (and optionally, columns) down within a group of cells.
=INDEX(range of cells, rows from top, [columns from left])If we were to rewrite the VLOOKUP earlier as an INDEX function, it would resemble the formula below:
In this formula, we are specifying that we want the second row down, and the second column within the range of A1 to H3. We do not need to index on the whole table though!
In this example, the index is only for column A, the account number. We specify that we are looking for 2 rows down, but do not need to provide a column number.
The MATCH function
The MATCH function works similar to a 1 column VLOOKUP function. It takes a value that you select and tells you the position that the match is within the range of cells that you select.
Like a VLOOKUP, you need to specify in the formula if you are looking for an exact match (0) or the largest value less than or greater than the target value (-1 or 1). You will rarely, if ever, use any argument other than 0.
=MATCH(value to match, range to look in, 0)If used to match results in a column, it should look like the formula below:
However; the MATCH function can also be used to match results from within a range of rows as well!
Using INDEX / MATCH.
Combined together, INDEX and MATCH provide a range to find results in, as well as co-ordinates based on matching criteria (potentially in another range).
Since the 2 ranges do not have to overlap, this can be used to look to the left of the range containing the value that you are trying to lookup.
In the example below, the formula is trying to find the account number for the account name Cash, in cell B6.
It uses the Index function to provide the list of cells that are trying to be found (the account numbers). Then, it uses the Match function to determine that the value Cash is 2 rows from the top.
The second row, looking in the INDEX function is the account number, 1000-0001.
INDEX / MATCH with rows and columns
In a more complex example, the INDEX / MATCH function can be used to provide not only the row within the INDEX, but also the column!
By increasing the size of the INDEX to include a number of columns as well as rows, the columns can also be added to the INDEX, similar to a VLOOKUP function.
The first MATCH function provides the row co-ordinate within the INDEX function, and the second MATCH function provides the column co-ordinate.
In a case like this, you can even use the VLOOKUP function; but we can use our second MATCH function to provide the column number for the VLOOKUP! No more counting columns!
Conclusion
The INDEX / MATCH function has been a popular function combination for many years – it is a fast performing function that overcomes some of the limitations that are inherent in VLOOKUP.
In 2020, the new XLOOKUP function will become broadly available in Office 365.
Will it be the formula that eliminates the INDEX / MATCH formula combination? Time will tell!