FormulaCraft

Fix the Wrong value error

Excel & Google Sheets
Topic:VLOOKUP

What Wrong value means

VLOOKUP found a row and returned a value, but it is not the value you expected. This happens when the formula is technically valid but one of its arguments is logically wrong — most often the wrong column index number, or approximate-match mode accidentally matching the wrong row in unsorted data.

Common causes

Example fix

Broken
=VLOOKUP(A2,$D$2:$G$100,2,TRUE)
Fixed
=VLOOKUP(A2,$D$2:$G$100,3,0)

Changed the fourth argument from TRUE (approximate match on potentially unsorted data) to 0 (exact match), and corrected the column index from 2 to 3.

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

How to fix it

  1. 1Count your columns from the start of table_array, not from column A. If table_array is D:G and you want column F, the index is 3, not 6.
  2. 2Always use 0 (or FALSE) as the fourth argument for exact matching unless your data is intentionally sorted and you need approximate match.
  3. 3Check for duplicate values in the lookup column with COUNTIF: =COUNTIF($D$2:$D$100,D2)>1. Decide whether to use the first match or restructure your data.
  4. 4Anchor the table range with $ signs: $D$2:$G$100 so the range does not shift when you copy the formula across rows or columns.
  5. 5After inserting or deleting source columns, audit every VLOOKUP's col_index_num. Consider switching to INDEX/MATCH or XLOOKUP where the return column is specified by reference, not by number, so insertions do not break it.

Stop hunting errors by hand.

Upload your spreadsheet and the Auditor flags every Wrong value and broken formula at once — or paste this one formula and get the fix explained.

Frequently asked

VLOOKUP is returning a value from a completely different row — what is happening?

You almost certainly have approximate match mode on (TRUE or omitted). On unsorted data, approximate match does a binary search and can land on any row. Set the last argument to 0 or FALSE.

My table has duplicate IDs and VLOOKUP keeps returning the first one — is there a way to get the second or last match?

VLOOKUP cannot do this natively. Use INDEX/MATCH with a helper column or, in Excel 365/Google Sheets, use FILTER or XLOOKUP which support returning multiple or last matches.

I inserted a column into my source data and now all my VLOOKUPs are wrong — how do I prevent this?

Switch to XLOOKUP or INDEX/MATCH. Both reference the return column by cell reference rather than a count, so inserting columns into the table does not break the formula.

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-to

How 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 fix

Fix #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-to

How 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 fix

Fix VLOOKUP

VLOOKUP not working in Excel and Google Sheets — diagnose formula errors, wrong syntax, range issues, and when to switch to a better alternative.

How-to

How to fix VLOOKUP returning #N/A

VLOOKUP shows #N/A when it can’t find a match. Here are the real causes — exact-match, spaces, text-vs-number — and how to fix each.

Related formulas

VLOOKUPXLOOKUPINDEX / MATCHINDEXMATCH

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: