Skip to content

INDEX MATCH instead of VLOOKUP

G22nd January 2015

C1 Comment

TExcel, Microsoft Office

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:

  • {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”

  • Written by Clem Stanyon on 23 January 2015:

    Thanks Adam,

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

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.