MrExcel Publishing
Your One Stop for Excel Tips & Solutions


September 18, 2017 - by Bill Jelen


Excel's VLOOKUP - can it return a value to the left of the key field? Can you specify a negative value for the third argument? Learn some alternatives in today's article.

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

VLOOKUP Doesn't Work with Negative Column Numbers
VLOOKUP Doesn't Work with Negative Column Number

The solution is to use a MATCH to find where the name is located, and then use INDEX to return the correct value.

Solution is Using MATCH and INDEX
Solution is Using MATCH and INDEX

VLOOKUP was suggested by Rod Apfelbeck, Patty Hahn, John Henning, @ExcelKOS, and @tomatecaolho. INDEX/MATCH 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.

Poster Credit: Bobby Rosenstock
Poster Credit: Bobby Rosenstock - justAjar Design Press

Watch Video

  • VLOOKUP can not look to the left of the key field!
  • Use MATCH to find where the item is located, then use INDEX to retrieve the lookup value.
  • Or - in real life, rearrange the lookup table
  • This episode features an original letterpress poster from Bobby Rosenstock at

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2029 vlookup left I see this is tip 31c
  • see it's a bonus tip between 31 and 32
  • feel bad about putting this tip in the
  • book but all right here we are
  • everything this book will be in the mr
  • excel excel playlist click that I on the
  • top right hand corner to get to the
  • whole playlist today we have a bizarre
  • situation where we have to do a vlookup
  • left the key field is an e and what we
  • have to look up is it d and you cannot
  • say vlookup of a 2 comma F 4 comma minus
  • 1 no you cannot do it all it would be
  • really cool if you could win it right
  • and that's why we had Bobby Rosenstock
  • it just a jar design press create this
  • awesome letterpress poster force of
  • value to the left is vlookups kryptonite
  • this message brought to you by index and
  • match as right index and matches yet
  • another way to solve this problem and
  • here's how it goes we use a match equal
  • match figure out where Alex is in the
  • list f 4 comma 0 for an exact match and
  • that tells us that Alex is in Row 3 and
  • for everyone else it tells us where it
  • is now that we know where it is we're
  • going to use the index function to
  • figure out which department they're in
  • so here at the beginning right after the
  • equal sign equal index of these
  • departments press f4 comma which one we
  • want we want the answer from the match
  • go to the end of the function close
  • parenthesis and we should have our
  • answer and double click to copy that
  • down simple no it's not simple because
  • most people never use match in most
  • people never you never use index sure
  • out of every hundred people i run into
  • who use vlookup one or two of us say
  • look index and match is so far superior
  • because it'll go left right in and they
  • say you know we're going to use vlookup
  • anymore but here's the thing out of that
  • hundred people use vlookup a hundred of
  • them know how to use vlookup but only to
  • have knew how to use index and match so
  • if your what your spreadsheets to be
  • explanatory easy to understand by
  • someone else use vlookup in here let's
  • face it this is why I feel bad including
  • some tip here's what we do in real life
  • control x control V and then and then
  • just do the vlookup double-click easy
  • all right so there are 40 tips in this
  • book that are awesome and a bunch of
  • bonus tips most of them are awesome this
  • one I don't like it all but it's in
  • there maybe you just tear that page out
  • but then you lose the thing on the page
  • behind it I guess just cross it out or
  • skip that one over however you want to
  • do it but either way go by the book
  • click the eye on the top right hand
  • corner alright this is a short episode
  • vlookup cannot look to the left of the
  • key field so a lot of people say to use
  • a match and then put that match inside
  • the index or I say just rearrange the
  • lookup table also the next Bobby
  • rosenstock for that cool cool poster I
  • think I still have a few of these left
  • but they were a bonus for the IndieGoGo
  • program i still have you these left
  • we're not off from around the holidays
  • as a package the mr excel playing cards
  • and the posters watch for that hey
  • thanks for stopping by i will see you
  • next time for another net cast from mr
  • excel

Download File

Download the sample file here: Podcast2029.xlsx

Title Photo: aitoff / Pixabay

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.