Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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”:

Name Date Amount
Jane 23 October 2014 $3.25
Sarah 30 October 2014 $6.20
Jane  4 November 2014 $10.25
Jane  20 November 2014 $4.90
Lucy 28 November 2014 $1.50
Sarah 28 November 2014 $9.45

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:

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: Hyperlink chain icon

Thanks Adam,

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

Reply

Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.