Confident Cells

VLOOKUP: a simple worked example

20 November 2026 · 3 min read

VLOOKUP looks for a value in the first column of a table, then brings back the value from another column on the same row. It's how you pull a price, a name or a team from one list into another.

A worked example

Here's a small price list. Each product has a code, a name and a price.

ABC
1CodeProductPrice
2P-204Stapler£6.50
3P-117Desk lamp£18.00
4P-310Box files£4.20
5P-152Whiteboard£35.00
6P-268Hole punch£7.75

Someone orders product P-310. To find its price by hand, you would:

  1. Go down the first column until you find P-310. It's on the third row of the list.
  2. Stay on that row and move across to the third column, Price.
  3. Read the price: £4.20.

That's exactly what VLOOKUP does. You tell it what to look for, where the table is, which column to bring back, and that you want an exact match.

The formula

With the order codes in column A and the price list in E1:G6, the price for the code in A2 is:

=VLOOKUP(A2,$E$2:$G$6,3,FALSE)
ABCDEFG
1ItemQtyPriceCodeProductPrice
2P-3104£4.20P-204Stapler£6.50
3P-1521£35.00P-117Desk lamp£18.00
4P-2042£6.50P-310Box files£4.20
5P-152Whiteboard£35.00
6P-268Hole punch£7.75
C2 holds =VLOOKUP(A2,$E$2:$G$6,3,FALSE), copied down to C4.

Copying it down: lock the table with $

The dollar signs in $E$2:$G$6 keep the table in place when you copy the formula down. Without them, the copy in C3 would look in E3:G7, so the first product drops out of its table and some lookups fail with #N/A. Click on the table reference in the formula bar and press F4 to add the dollar signs (more on this in absolute references). The code, A2, has no dollar signs because it should move down a row each time.

Why VLOOKUP goes wrong

#N/A: it can't find the value

The value isn't in the table's first column, or it looks the same but isn't. The usual causes are a space at the end ("P-310 ") or a number stored as text. TRIM removes stray spaces. If a missing value is expected, wrap the lookup in IFERROR to show a message instead:

=IFERROR(VLOOKUP(A2,$E$2:$G$6,3,FALSE),"Not found")

A wrong answer with no error

You left off FALSE. Without it, VLOOKUP does an approximate match, which expects the first column to be sorted and can quietly bring back a value from the wrong row. Approximate match is only for bands, such as finding the grade for a score or the rate for a salary.

#REF!: the column number is too big

A table three columns wide has no column 4. Check the number against the table you selected, not the whole sheet.

It brings back the wrong column after you insert one

The column number is just a number, so inserting a new column inside the table doesn't update it. Check the 3 still points at the column you want.

It can't look to the left

VLOOKUP only searches the first column of the table and only brings back columns to its right. To find a code from a product name, use XLOOKUP or INDEX with MATCH.

Try it now

Your turn

In C2, look up the price of each item from the price list in E1:G6. Your formula will be copied down to C7.

ABCDEFG
1ItemQtyPriceCodeProductPrice
2P-3104P-204Stapler£6.50
3P-1521P-117Desk lamp£18.00
4P-2042P-310Box files£4.20
5P-2683P-152Whiteboard£35.00
6P-1171P-268Hole punch£7.75
7P-3106

Where it works

The same formula works in every version of Excel, in Google Sheets and in LibreOffice Calc. In Excel for Microsoft 365 and Excel 2021 onwards, XLOOKUP does the same job without counting columns, and can look left.