Why VLOOKUP returns #N/A

Inside the formula

Example data

How this VLOOKUP formula works

VLOOKUP searches D2:D7 for the value in A2. Exact-match mode refuses to settle for a neighboring ID, so the missing P-999 produces #N/A.

The top row above is the whole formula. Open any argument that holds a calculation of its own to see the values it used. Excel works out those inner parts first, because the function around them needs their results. Edit a cell in the grid to see every value update.

Try your own formula and data

The sample spreadsheet is fully editable. Change the formula or any cell it references. The intermediate values recalculate in your browser.

Practice with an exercise

Here is an exercise that uses VLOOKUP. Type the formula into a real spreadsheet and we will check your answer as you go.

Use VLOOKUP to find an employee’s department

Beginner

Look up an employee's department using their employee ID.

Open exercise

Common mistakes with VLOOKUP

If this formula is not doing what you expect, one of these is usually why.

Why does my VLOOKUP return #N/A?

The lookup value is absent from the first column of the table, or Excel considers it different. Trailing spaces and numbers stored as text are common causes because both can look correct on screen.

Why does VLOOKUP return the wrong value?

The last argument is TRUE or missing, so VLOOKUP settles for an approximate match. Use FALSE unless the first column is sorted and you deliberately want a banded lookup.

Why do I get #REF! from VLOOKUP?

The column number is larger than the table is wide. D2:E7 is two columns wide, so 3 has nowhere to go. Count from the left edge of the selected table.

Can VLOOKUP look to the left?

No. VLOOKUP always searches the first column of the table and returns something to its right. Use INDEX and MATCH or XLOOKUP when the answer is to the left.