Why VLOOKUP Returns #N/A (and 4 Ways to Fix It)
A VLOOKUP that shows #N/A even though the value is in the list usually has one of four causes. Find and fix each one on a small order sheet: a missing FALSE, a table range that stops too early, hidden spaces, and numbers stored as text.
Try it yourself
Copy these tables into a blank sheet, then follow the steps below.
Paste at cell A1:
| Code | Product | Price |
|---|---|---|
| 1005 | Monitor Stand | $45 |
| 1002 | Office Chair | $149 |
| 1006 | Wall Clock | $22 |
| 1001 | Desk Lamp | $35 |
| 1004 | Stapler | $12 |
| 1003 | Notebook | $4 |
| 1007 | Desk Fan | $29 |
Paste at cell E1:
| Order | Code | Price |
|---|---|---|
| ORD-1 | 1001 | |
| ORD-2 | 1007 | |
| ORD-3 | '1002 | |
| ORD-4 | '1003 |
Step by step
- VLOOKUP shows #N/A. Your VLOOKUP says #N/A, but the code is right there in the list. That error almost always comes from one of four causes. Let's find and fix each one in Excel.
- The product list. Here's the product list. A code, a product and a price. The desk fan in the last row was added this week.
- The order list. And four orders, each with a product code. We want each price in column G.
- The first try. Here's the VLOOKUP many people write first. The code, the table, and column three for the price. Fill it down. Order one shows #N/A, even though code one oh oh one, the desk lamp, is in the list. And order two shows four dollars, the wrong price.
G2
=VLOOKUP(F2,$A$2:$C$7,3) - Exact match. Cause one, no exact match. Leave out the last part, and VLOOKUP guesses, as if the list were sorted. Ours isn't. Add a comma and false. Fill it down. Thirty five dollars for the desk lamp. But order two now says #N/A.
G2
=VLOOKUP(F2,$A$2:$C$7,3,FALSE) - The range. Cause two, the table range is too short. Order two wants the desk fan, in row eight. Our table stops at row seven. Change the seven to an eight, so the table covers every row. Fill it down. Twenty nine dollars for the fan.
G2
=VLOOKUP(F2,$A$2:$C$8,3,FALSE) - Hidden spaces. Cause three, hidden spaces. The code for order three has a space at the end. You can't see it, but Excel can. Wrap the code in TRIM, which cuts extra spaces. Fill it down. Wait, now every order says #N/A! TRIM always gives back text, and text never matches a real number in the list.
G2
=VLOOKUP(TRIM(F2),$A$2:$C$8,3,FALSE) - Text numbers. Cause four, numbers stored as text. See how the last two codes sit on the left of the cell? That means text. Wrap it all in VALUE, which turns text into a real number. Fill it down one last time. One hundred forty nine for the chair, four dollars for the notebook, and no more #N/A.
G2
=VLOOKUP(VALUE(TRIM(F2)),$A$2:$C$8,3,FALSE) - Mark the formulas. Give the formula cells a light fill, so the team knows not to type over them.
- 4 causes of #N/A. So, when VLOOKUP says #N/A, check four things. Add false for an exact match. Make the table cover every row. TRIM hidden spaces. And turn text into numbers with VALUE.
