Tutorial: The difference between vlookup and Index Match

By | February 28, 2015

First let me start with that VLOOKUP is a bit simpler and quicker to put together and has its time and place.

Sometimes though, we need to lookup a value that might be to the left of the search column (remember, VLOOKUP only searches from left to right).

Rumour has it that Index Match also works faster in big spreadsheets but I don’t notice much of a difference.

A big difference between VLOOKUP and Index Match is that Index Match will update automatically if you insert columns into your lookup data. So if your lookup tables are likely to change, you may want to consider using Index Match.

Here’s the structure of the Index Match formula:

=index(WhichColumnToReturnValueFrom , (match(SearchForValue, SearchColumn, 0)

=INDEX ( Column I want a return value from , ( MATCH ( My Lookup Value , Column I want to Lookup against , ZeroForExactMatch)

So an example would be if we had a student name and wanted to look up their student number (column to the left) – see below, in which the result returned would be Meggan’s student number A0004

index match example

 

Also, check out Eleven to Three, a website providing tutorials on many Excel functions including index match (http://www.eleventothree.com/excel/using-index-match/

Leave a Reply

Your email address will not be published. Required fields are marked *