Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to fix VLOOKUP returning #N/A in Excel and Google Sheets
Topic:VLOOKUPExcel & Google Sheets
=VLOOKUP(D2, A:B, 2, FALSE)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Make sure the last argument is FALSE (or 0) to force an exact match.
- 2Confirm the lookup value actually exists in the first column of the table range.
- 3Match the data types — a number stored as text won’t equal a real number. Convert with VALUE() if needed.
- 4Strip stray spaces with TRIM on both the lookup value and the source.
Tips
- The lookup column must be the leftmost column of the table range; if not, use INDEX/MATCH or XLOOKUP.
- To show a friendly message instead of #N/A, wrap it: =IFNA(VLOOKUP(...), "Not found").
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why does VLOOKUP show #N/A when I can clearly see the value?
Usually a hidden formatting difference — text vs number, or trailing spaces. The values look identical but aren’t equal. Use TRIM and make the data types match.
Does the lookup column have to be on the left?
For VLOOKUP, yes — it only searches the first column of the table. To look left, use INDEX/MATCH or XLOOKUP instead.
More on VLOOKUP
See all →Reference
VLOOKUP
Look up a value in the first column of a range and return a value from another column in the same row.
How-toHow to VLOOKUP from another sheet
Pull a value from a different tab with VLOOKUP by prefixing the range with the sheet name. Works in Excel and Google Sheets.
Error fixFix #N/A
Fix #N/A errors in VLOOKUP in Excel and Google Sheets — covers missing matches, extra spaces, data-type mismatches, and range mistakes.
How-toHow to VLOOKUP with multiple criteria
Look up a value that matches two or more conditions using INDEX/MATCH with joined keys. Works in Excel 365 and Google Sheets.
Error fixFix Wrong value
VLOOKUP returns an incorrect value in Excel and Google Sheets — fix wrong column index, approximate match mode, unsorted data, and duplicate lookup keys.
How-toXLOOKUP vs VLOOKUP: which to use
XLOOKUP looks in any direction, defaults to exact match, and has a built-in not-found value. Here is when to use each over VLOOKUP.