VLOOKUP #N/A Error Fix: When the Key Is in the Table and Excel Says No

A key containing * or ? is read as a wildcard even with FALSE in the fourth argument, so looking up *SAVE10 returns the first row that ends in SAVE10 along with that row's price. Put a tilde in front (~*) to match the character itself. The returned price looks reasonable, which is why the row survives review.

MATCH and XMATCH return #N/A for the same reasons, so an INDEX + MATCH pair built on a bad key fails in the same cell position. Replace the lookup function and nothing changes; the fault is in the key.

Google Sheets returns #N/A for both faults and its VLOOKUP takes the same four arguments, with is_sorted defaulting to TRUE. The functions behave alike here. The diagnostics do not, since Sheets has no Evaluate Formula to step through the arguments.

The reverse direction needs VALUE. A key pasted from a web form arrives as text while the table holds real numbers, and there the formula is =VLOOKUP(VALUE(G2),$A$2:$C$9,3,FALSE). Match the wrapper to whichever side is text rather than memorizing one of them.

Repairing the column once beats wrapping forty formulas. Select the SKU column, open Data > Text to Columns, and click Finish without picking a delimiter; the whole column converts in place. For the other direction, type a 0 in an empty cell, copy it, select the column, and use Paste Special > Multiply.

A column that is part number and part text, because someone retyped a few cells by hand, produces a lookup that works on some rows and fails on others. Sorting it is not a repair: text numbers sort 1000 before 999, so the row order changes while every cell stays text.

CLEAN does not rescue this one. Excel's CLEAN strips codes 0 to 31, Google Sheets adds 127, and 160 survives both, so TRIM(CLEAN(H2)) misses the same vendor in both applications.

A whole column is fixed faster with Find & Replace. Paste the character into Find, put a normal space in Replace, and run it over the vendor column. Alt+0160 on the numeric keypad produces one when the clipboard is empty.

When the non-breaking space sits in the table rather than in the key, no wrapper helps: SUBSTITUTE would have to run over table_array, and VLOOKUP will not accept that on a plain range. Clean the column, then leave the formula alone.

A table that grew past its range is the quiet version. Add a 100 lb band in row 7 and a 120 lb shipment still returns 44.50, because table_array stops at row 6. Excel extends an anchored range when rows are inserted inside it, but not when rows are added below it.

Sorting the first column into descending order breaks approximate match without putting an error on the screen. Excel bisects that column assuming it ascends, so the band returned depends on where the key falls in the bisection, and keys that returned a rate yesterday start returning #N/A.

Google Sheets defaults is_sorted to TRUE with the same consequence, and its help page states the range must be sorted for the sorted form. Copy a VLOOKUP between the two applications and the fourth argument carries the fault with it.

An empty string looks tidier than a label and cannot be counted on its own. COUNTIF(range,"") returns empty-string cells and truly blank cells in one number, so the misses and the gaps become the same figure.

IFNA needs Excel 2013 or later and has been in Google Sheets since 2014. Its second argument is required in Excel and optional in Sheets, where leaving it out returns an empty string.

Put the wrapper on last. Wrapping on day one converts a data fault into a displayed value, and the report stays wrong for as long as nobody questions a column of Not found.