MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Lookup to the Left with INDEX/MATCH


August 19, 2019 - by Bill Jelen

Excel Lookup to the Left with INDEX/MATCH. Photo Credit: Devin Avery at Unsplash.com

What if your lookup value is to the right of the information that you want VLOOKUP to return? Conventional wisdom says VLOOKUP cannot handle a negative column number in order to go left of the key.

You are looking up names and want the department. But the lookup table has Department on the left and Name on the Right. It would be nice if you could =VLOOKUP(A2,Table,-1,False) but you can not specify -1 as the column to return.

One solution is =VLOOKUP(I7,CHOOSE({1,2},G1:G5,F1:F5),2,0). However, I prefer to use MATCH to find where the name is located and then use INDEX to return the correct value.


The solution is to use INDEX and MATCH. =INDEX($D$4:$D$11,MATCH(A2,$E$4:$E$11,0)).

The INDEX/MATCH trick came from Mark Domeyer, Jon Dow, Justin Fishman, Donna Gilliland, Alex Havermans, Jay Killeen, Martin Lucas, Patrick Matthews, Mike Petry, Michael Tarzia, and @beatexcel. Thanks to all of you.

A letterpress poster says:  "A value to the LEFT..... VLOOKUP'S KRYPTONITE". The poster continues with This message brought to you by INDEX & MATCH: INDEX(X2:X99,MATCH(A2,Z2:Z99,0)). This is an advertising poster for MrExcel.com - your 1 stop for Excel solutions.
Poster Credit: Bobby Rosenstock justAjar Design Press, http://www.justajar.com/

Title Photo: Devin Avery at Unsplash.com


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.