Excel 2020: Lookup to the Left with INDEX/MATCH

August 20, 2020 - 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

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.