VLOOKUP Basics (and the Four Errors You'll Hit First)

You have a list of SKUs in column D and a product table on another sheet. You want the price in column E without scrolling. VLOOKUP is the classic tool — when it breaks, it is usually one of four predictable mistakes.

This walkthrough uses Excel menu names. In Google Sheets the function is the same; the formula bar still shows =VLOOKUP(...).

Set up the two tables

Sheet Orders — starting at A1:

Sheet Products — starting at A1:

Click E2 on Orders (the Price column). You will write the lookup here, then fill down.

Write the formula

=VLOOKUP(D2, Products!A:D, 4, FALSE)

Breakdown:

• D2 — lookup value (the SKU)
• Products!A:D — table where column A holds SKUs
• 4 — return the 4th column of that range (Price)
• FALSE — exact match (required for SKUs)

Press Enter. Copy down: select E2, Ctrl+C, select E2:E100, Ctrl+V (Excel). Google Sheets: drag the fill handle or Ctrl+D after selecting the range.

Shortcut: With E2 selected, double-click the fill handle (small square at cell corner) to copy to the last row of adjacent data in column D.

Wrong vs Right

Fix #N/A

#N/A means the lookup value is not found in the first column of the range.

Checklist:

1. Click the failing cell. Press F2 to edit. Does D2 exactly match Products!A2? No extra spaces?
2. Is the SKU on Products at all? Sort A:A and scan, or use Ctrl+F on the Products sheet.
3. Are numbers stored as text? A SKU W-104 in D2 will not match number 104.

Quick test in an empty cell:

=COUNTIF(Products!A:A, D2)

Should return 1. If 0, the value is not in the list.

Fix #REF!

#REF! often means the column index is too large for the range, or a sheet name changed.

• Range A:D has 4 columns. Index must be 1–4.
• Renaming Products breaks formulas that still say Products!. Use Find & Replace (Ctrl+H) across the workbook if you rename sheets often.

When to move on

If your lookup column is not the leftmost column, or you need to look left, VLOOKUP gets awkward. Read XLOOKUP: when to use it instead of VLOOKUP next.

For summarizing sales by category after prices are filled in, see pivot table intro.