INDEX MATCH instead of VLOOKUP

INDEX MATCH represents a much better way to search for data in Excel tables than VLOOKUP because this combination is much more versatile.

One of the irritating problems with Microsoft Excel’s VLOOKUP function is that it relies upon the search term being in the first column of the table where the result is.

This is very inconvenient and can complicate table design.

I’ve previously posted an article describing how to use INDEX and MATCH to search multiple parameters. Now I will show you how to use INDEX and MATCH functions to replace VLOOKUP altogether when searching for single parameters. This will enable you to match a search string from any column.

For this demonstration, I shall use previous data.

Example Data

This is the table from my previous post where the formatted table is called “Loans”:

Let’s say that I have a second table with dates in a column called “Date of Loan”. I want to search the “Loans” table for the amount of money that was lent on 20 November 2014 by matching it to the date in my new table.

I cannot use a VLOOKUP because my search string (“20/11/2014”) is in Column B. The following code, whist syntactically correct, produces a #N/A error because “20/11/2014” doesn’t appear in Column A of the “Loans” table.

=VLOOKUP([Date of Loan], Loans, COLUMN(Loans[Amount]), FALSE)

The solution is as follows:

=INDEX(Loans[Amount],MATCH([@[Date of Loan]],Loans[Date], 0))

Understanding the formula

This formula can be read as follows:

=INDEX({A},MATCH({B},{C}, 0))

where:

• {A} = Reference to the column where the answer can be found
• {B} = Reference to the search string in the current table
• {C} = Column in the other table where the matching search term can be found

This type of formula can be used in all the same situations as a VLOOKUP.

Conclusion

Whilst the use of INDEX and MATCH code may appear to be more complicated than VLOOKUP at first, this combination is much more versatile. In fact, some have described multiple advantages when using INDEX and MATCH with cell references.

Hopefully this post has helped you understand the basics of INDEX and MATCH.

Comments

One response to “INDEX MATCH instead of VLOOKUP”

On 23 January 2015, Clem Stanyon wrote:

Thanks Adam,

That’s a very clear and simple explanation. Much obliged.

Reply

Have Your Say

Δ