Using INDEX & MATCH instead of VLOOKUP

I’ve been using Excel since Office 97. I was about 8 years old, and had no idea what I was doing. By the time I had progressed to VLOOKUPs and PivotTables, I was a bit older (and wiser, I’d like to think).

It was to my chagrin that I discovered about 2 years ago, that instead of creating a clusterbomb of VLOOKUP/CHOOSE (sometimes with some nested IFs sprinkled in for good measure), I could have been doing INDEX/MATCH all along.

=INDEX(x, MATCH(y, z, 0))

x = Range to return a value from
y = Value to look up
z = Range to look up the value in

I’ve made a small spreadsheet to demonstrate. Please ignore the fact that copying the formulas from Excel Online has changed them to the American region structure, hence the semi-colons as delimiters instead of commas.

Cell E2 does the standard VLOOKUP

=VLOOKUP(D2;$A$2:$B$9;2;FALSE)

while F2 uses INDEX/MATCH to achieve the same thing

=INDEX($B$2:$B$9; MATCH(D2;$A$2:$A$9;0))

That looks a bit more complex than the VLOOKUP. The real power is demonstrated in the next example. To achieve E3, VLOOKUP does the following

=VLOOKUP(D3; CHOOSE({2\1};$A$2:$A$9;$B$2:$B$9);2;FALSE)

while INDEX/MATCH follows the same structure as before in F3

=INDEX($A$2:$A$9; MATCH(D3;$B$2:$B$9;0))

This is why I am now more inclined to use INDEX/MATCH. The ability to do a lookup in any column, and return a value from any column, is great. This really helps for those times when I’m not just rather loading the data from the spreadsheets into Python dictionaries and pulling it apart there.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s