VLOOKUP: Get a Value Based on Another Column

VLOOKUP answers one question: given a value in the first column of a table, return something from another column of the same row. Product code to price, employee number to department, SKU to stock level.

The syntax

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

To find the price for the code in A2, in a table sitting in $F$2:$H$500 with prices in the third column:

=VLOOKUP(A2, $F$2:$H$500, 3, FALSE)

Always supply FALSE (or 0) as the fourth argument unless you specifically want an approximate match. Omitting it defaults to TRUE, which requires the first column to be sorted ascending and otherwise returns confidently wrong answers rather than an error. Approximate match has legitimate uses — tax bands, volume discounts, grade boundaries — but it should be a deliberate choice.

Why it returns #N/A

• The value genuinely is not there. Wrap it: =IFERROR(VLOOKUP(...), "Not found").
• Numbers stored as text. A code typed as text will not match the same code stored as a number. Select the column and use Text to Columns, or Data → Text to Columns → Finish, to force conversion.
• Trailing spaces. TRIM() on the lookup value or the source column fixes it.
• The lookup value is not in the first column of the table. VLOOKUP always searches the leftmost column of table_array; it cannot look to the left.
• The table reference moved when you filled down. Lock it with $ signs, or convert the source to an Excel table and use its name.

What to use instead

In current versions of Excel, XLOOKUP does the same job with fewer traps: it defaults to exact match, takes a “if not found” argument directly, and can look in any direction.

=XLOOKUP(A2, $F$2:$F$500, $H$2:$H$500, "Not found")

INDEX/MATCH remains the portable alternative that works in every version and also looks leftward:

=INDEX($H$2:$H$500, MATCH(A2, $F$2:$F$500, 0))

Learn VLOOKUP because you will meet it in other people’s workbooks and in exams; write new formulas with XLOOKUP where your audience’s Excel version supports it.

A worked example

Say your price list sits in F2:H500: product codes in F, descriptions in G, prices in H. In A2 you have the code WID-204 and you want its price in B2.

=VLOOKUP(A2, $F$2:$H$500, 3, FALSE)

The 3 counts columns from the left edge of the table, not from column A of the sheet. F is column 1, G is 2, H is 3. Counting from the wrong starting point is the single most common cause of a VLOOKUP returning the right row but the wrong field.

If you insert a column inside the table later, that hard-coded 3 silently points somewhere else. Two defences: convert the source to an Excel Table and reference it by name, or replace the number with MATCH("Price", $F$1:$H$1, 0) so the column index looks itself up by header.

Looking up on two or more criteria

VLOOKUP takes exactly one lookup value. When you need the price for a product in a particular region, or a rate for an employee in a given year, you need a different approach — a concatenated helper column, INDEX/MATCH with boolean multiplication, or XLOOKUP over a combined array. All four methods are set out in lookup with multiple criteria.

Wildcards in the lookup value

With FALSE as the fourth argument, VLOOKUP accepts two wildcards: * for any number of characters and ? for exactly one.

=VLOOKUP("WID-*", $F$2:$H$500, 3, FALSE)

Useful for partial codes and messy reference data. Two cautions: it returns the first match only, so it is unreliable when several rows could match; and if your data genuinely contains an asterisk or question mark, escape it with a tilde — ~*.

The other error: #REF!

#N/A means “not found”. #REF! means something different and is always a formula fault: the col_index_num is larger than the number of columns in table_array. Asking for column 4 of a three-column table produces it every time.

It usually appears after someone deletes a column from the source data, or after copying a formula that was written against a wider table. Count the columns in your range and compare with the index number.

XLOOKUP, VLOOKUP, HLOOKUP, INDEX and MATCH are all named in the MOS Excel Expert skills outline — see our MO-211 exam guide.