MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.


Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.