VLOOKUP explained properly

blank

VLOOKUP is one of the most widely used formulas in Excel, and also one of the most commonly muddled up, mostly because the four pieces of information it needs are easy to mix up if you haven’t written many of them.

What it actually does

VLOOKUP searches for a value in the first column of a range and returns a value from another column on that same row.

In plain terms, you give it something to search for, tell it where to search, and tell it what to bring back. VLOOKUP stands for Vertical Lookup, since it works down columns, there’s also a lesser used HLOOKUP for horizontal data, and it’s just as ACE, simply not needed as often. There is also a new swanky XLOOKUP too…….watch out for that coming soon in a later blog post.

VLOOKUP: What it actually does (step 1)

The four parts

The formula is written as

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup).

lookup_value is what you’re searching for

table_array is the range to search AND return from (freeze it with $ signs so it doesn’t shift when you copy the formula)

col_index_num is which column in that range to return, counting from the first column of your chosen range, not from the start of the whole sheet

range_lookup decides whether you want an exact match (FALSE) or the nearest match (TRUE, which requires the data to be sorted first).

VLOOKUP: The four parts (step 2)

VLOOKUP: The four parts (step 3)

VLOOKUP: The four parts (step 4)

VLOOKUP: The four parts (step 5)

VLOOKUP: The four parts (step 6)

The do’s and don’ts

  • Do make sure the value you’re searching for exists in the FIRST column of your chosen range, VLOOKUP can only search left to right.
  • Do specify FALSE for exact matches, particularly with text or codes.
  • Do double check your column number, it won’t update automatically if columns are inserted or deleted.
  • Don’t forget to freeze your table reference with $ if you’re copying the formula down or across.
  • Don’t overlook trailing spaces, VLOOKUP can’t find a match if there’s an invisible space at the end of your text, wrap the lookup value in TRIM() to fix it.

Where it falls down

VLOOKUP only searches left to right, so if the column you want to return sits before your lookup column, it simply won’t work.

It’s not case sensitive, so it treats ABC and abc as identical, and performance can slow down noticeably on bigger datasets.

When it can’t find a match it returns an unsightly #N/A, so it’s often paired with IFERROR to return something more useful instead.

If any of that sounds like a dealbreaker, XLOOKUP (or INDEX and MATCH combined) solves most of VLOOKUP’s limitations, worth knowing about even if VLOOKUP remains your everyday go to.

Used properly though, VLOOKUP is genuinely one of the most useful tools in your Excel toolkit, and once the four arguments click, you’ll wonder how you managed without it.

Share This Article

More To Explore

Join the Excel Ace community!
Get FREE Excel tips, guides, and industry insights delivered straight to your inbox. Sign up for our newsletter today!
Name