How to Fix the #N/A Error in Google Sheets (VLOOKUP, MATCH, XLOOKUP)

By Gerard Fernandez · Updated · 3 min read

#N/A means "not available": a lookup function searched for a value and didn't find it. You'll see it with VLOOKUP, HLOOKUP, MATCH, XLOOKUP and INDEX/MATCH. Sometimes the value really isn't there; often it is, but something small stops Sheets from recognizing it.

What the error looks like

In our test sheet, the formula in F2 is =VLOOKUP(E2, A2:C5, 3, FALSE). It looks for product P-105, which isn't in the list (P-101 to P-104):

Google Sheets VLOOKUP in F2 returning #N/A with the tooltip Error: Did not find value 'P-105' in VLOOKUP evaluation
Real test: hovering shows exactly which value wasn't found.

The quotes in the message are useful: in another test we searched for "P-101 " with a trailing space, and the tooltip showed 'P-101 ' with the space inside the quotes. That's how you spot cause 2 below.

The five usual causes

CauseHow to check / fix
The value really isn't in the listSearch the column with Ctrl + F. If it's missing, the error is correct: handle it with IFNA (below).
Extra spaces, e.g. "P-103 "Clean the data with Data → Data cleanup → Trim whitespace, or use TRIM(E2) as the search key.
Number vs text: 101 and "101" don't matchNumbers sit on the right of the cell, text on the left. Make both sides the same type, e.g. VLOOKUP(TO_TEXT(E2), …) or VLOOKUP(VALUE(E2), …).
The search column isn't the first column of the rangeVLOOKUP only searches the first column. Start the range at the ID column, or switch to INDEX and MATCH.
Last argument missing or TRUE on unsorted dataAlways use FALSE (exact match) unless you're deliberately doing a sorted, approximate lookup.

Show a friendly message with IFNA

When "not found" is a normal result, replace the error with your own text:

=IFNA(VLOOKUP(E2, A2:C5, 3, FALSE), "Not found")
F2 still shows #N/A with plain VLOOKUP, while G2 with IFNA shows Not found
Real test: plain VLOOKUP in F2, the IFNA version in G2. IFNA catches only #N/A, so real mistakes elsewhere in the formula still show up.

Prefer IFNA over IFERROR for lookups. IFERROR would also hide a #REF! from a broken range, or a #NAME? from a typo, and you'd never know the formula was wrong.

XLOOKUP has this built in

XLOOKUP takes a "missing value" argument, so you don't need IFNA:

=XLOOKUP(E2, A2:A5, C2:C5, "Not found")

In our test, XLOOKUP for P-105 returned "Not found" straight away, with no wrapper.

Frequently asked questions

The value is clearly there. Why #N/A?

Nine times out of ten it's a trailing space or a number stored as text. Test with =A4=E2: if it returns FALSE when both look identical, the values aren't really the same. Still stuck? Work through the six checks in Fix "Did not find value in VLOOKUP evaluation".

How do I count how many lookups failed?

=SUMPRODUCT(--ISNA(F2:F100)) counts the #N/A cells in a column.

Where can I learn VLOOKUP from scratch?

See how to use VLOOKUP in Google Sheets.