How do I… · Excel

How do I use VLOOKUP in Excel?

VLOOKUP finds a value in the first column of a range and returns something from a column to its right. Four arguments, and the fourth is the one that goes wrong.

Steps

  1. Click the cell where you want the answer and type =VLOOKUP(.
  2. First argument: the value you are looking up — usually a cell reference like A2.
  3. Second: the range to search. Its first column must be the one holding the lookup value. Lock it with $ — $D$2:$F$400 — so it does not slide when you fill the formula down.
  4. Third: which column of that range to return, counted from 1 at the left of the range, not from column A of the sheet.
  5. Fourth: FALSE. This asks for an exact match. Leaving it out means approximate, which silently returns the wrong row on unsorted data.

Worth knowing

=VLOOKUP(A2, $D$2:$F$400, 3, FALSE) reads: find A2 in column D, return the third column of that range — column F. If you get #N/A, the value genuinely is not there, or one side has trailing spaces or is text where the other is a number. Wrap it in IFERROR(…, "") once you have checked, not before. If the value you are looking up sits to the right of what you want back, VLOOKUP cannot do it — use XLOOKUP, or INDEX/MATCH on older versions.

Related

Faster than searching: box the part of your screen you cannot work out and ask about it directly — in Excel or anywhere else. Answers arrive where you drew the box.

Try it on a screenshot Download free