Excel · ChatGPT

How to Fix VLOOKUP #N/A Errors in Excel with ChatGPT

Your VLOOKUP or XLOOKUP is returning #N/A but the value is right there in the table. Paste the formula and a few sample rows into ChatGPT — it spots the trailing space, the type mismatch, or the missing match argument and gives you the exact fix.

Choose your situation — copy the prompt: 
I have a VLOOKUP / XLOOKUP that is returning #N/A. Here is the formula:
[paste formula]

The lookup value is in cell [e.g. A2] and its current value is [paste the actual value].
The lookup table is on sheet [name], columns [e.g. A to D].
Here are 3 rows from the lookup table around where the match should be:
[paste rows]

When I replace the cell reference with the value typed directly, the formula [works / still returns #N/A].
Please identify why the match is failing and give me the corrected formula.
I think my #N/A error is caused by a data type mismatch — my lookup value appears to be a number but the table column might store it as text (or vice versa). Can you rewrite my formula to handle this?
[paste formula]
My formula is now working correctly. Can you wrap it in IFERROR so that when a value genuinely isn't found, the cell shows a dash "—" instead of #N/A?
[paste formula]
Before — hidden spaces make visible matches return #N/A
B2
fx=XLOOKUP(A2,$D$2:$D$5,$E$2:$E$5)
A
B
C
D
E
1
Lookup name
Revenue
Customer table
Revenue
2
Smith, John␠
#N/A
Smith, John
£4,250
3
Jones,␠␠Mary
#N/A
Jones, Mary
£7,800
4
␠Clark, Peter
#N/A
Clark, Peter
£6,100
5
Davis, Emma
£3,400
Davis, Emma
£3,400
Why it fails: A2 has LEN 12 while D2 has LEN 11. The extra trailing space is invisible in the cell but makes the text different.
→
After — TRIM() normalizes the lookup value
B2
fx=XLOOKUP(TRIM(A2),$D$2:$D$5,$E$2:$E$5)
A
B
C
D
E
1
Lookup name
Revenue
Customer table
Revenue
2
Smith, John␠
£4,250
Smith, John
£4,250
3
Jones,␠␠Mary
£7,800
Jones, Mary
£7,800
4
␠Clark, Peter
£6,100
Clark, Peter
£6,100
5
Davis, Emma
£3,400
Davis, Emma
£3,400
Verified: TRIM removes leading/trailing spaces and collapses repeated internal spaces. All four names now match; total returned revenue = £21,550.
Before — numeric lookup IDs do not match text IDs
B2
fx=XLOOKUP(A2,$D$2:$D$5,$E$2:$E$5)
A
B
C
D
E
1
Lookup ID
Salary
Payroll ID
Salary
2
1041
#N/A
'1041
£48,000
3
1087
#N/A
'1087
£52,000
4
1112
#N/A
'1112
£38,000
5
1203
#N/A
'1203
£44,500
Why it fails: A2 is the number 1041; D2 stores text "1041". They look the same on screen but XLOOKUP exact matching treats the types as different.
→
After — TEXT() converts the lookup ID to the stored type
B2
fx=XLOOKUP(TEXT(A2,"0"),$D$2:$D$5,$E$2:$E$5)
A
B
C
D
E
1
Lookup ID
Salary
Payroll ID
Salary
2
1041
£48,000
'1041
£48,000
3
1087
£52,000
'1087
£52,000
4
1112
£38,000
'1112
£38,000
5
1203
£44,500
'1203
£44,500
Verified: TEXT(A2,"0") converts numeric 1041 to text "1041", so the exact match succeeds. An alternative is to convert the source IDs in column D to numbers with VALUE().
Before — valid lookup, but genuinely missing IDs propagate #N/A
B4
fx=XLOOKUP(TEXT(A4,"0"),$D$2:$D$3,$E$2:$E$3)
A
B
C
D
E
1
Employee ID
Salary
Payroll ID
Salary
2
1041
£48,000
'1041
£48,000
3
1087
£52,000
'1087
£52,000
4
1301
#N/A
5
1405
#N/A
Expected missing values: IDs 1301 and 1405 are not in the payroll lookup range. A downstream =SUM(B2:B5) therefore also returns #N/A.
→
After — IFERROR returns a dash while valid salaries stay numeric
B4
fx=IFERROR(XLOOKUP(TEXT(A4,"0"),$D$2:$D$3,$E$2:$E$3),"—")
A
B
C
D
E
1
Employee ID
Salary
Payroll ID
Salary
2
1041
£48,000
'1041
£48,000
3
1087
£52,000
'1087
£52,000
4
1301
—
5
1405
—
Verified: text values "—" are ignored by SUM, so =SUM(B2:B5) returns £100,000 while the two missing IDs remain visible for review.
Why lookups return #N/A — the 4 most common causes
Trailing spaces
Lookup value is "Smith " but the table has "Smith"
Most common
Data type mismatch
Lookup value is 1042, table stores "1042"
Most common
Wrong match argument
VLOOKUP's fourth argument defaults to TRUE when omitted
Easy fix
Value genuinely missing
Item isn't in the table yet — use IFERROR(...,"—")
Expected

Common causes of #N/A errors and how to fix them

Diagnose and fix a #N/A error — 5 steps
1

Check the match argument

For VLOOKUP, check whether the fourth argument is FALSE. XLOOKUP uses exact match by default.

2

Compare the values in the formula bar

If two values look identical but do not match, check for spaces or data-type differences.

3

Run the manual test

Replace the lookup-cell reference with a hardcoded value. If that version works, investigate the source data.

4

Paste the evidence into ChatGPT

Include the formula, sample values, and the result of your manual test.

5

Apply the fix, then add IFERROR if appropriate

Use TRIM(), VALUE(), TEXT(), or an exact-match setting depending on the cause.

Frequently asked questions

Why does VLOOKUP return #N/A when the value exists?
VLOOKUP returns #N/A when it cannot find an exact match. Common causes include extra spaces, mismatched data types, or incorrect match settings.
How do I fix #N/A caused by trailing spaces?
Wrap your lookup value in the TRIM() function to remove leading and trailing spaces. For example, change =VLOOKUP(A2, D:F, 2, FALSE) to =VLOOKUP(TRIM(A2), D:F, 2, FALSE).
Does XLOOKUP still return #N/A errors?
Yes. XLOOKUP still returns #N/A when no exact match exists, including cases caused by spaces or different data types.
What is the fastest way to tell if #N/A is a formula problem or a data problem?
Try a hardcoded lookup value. If it works while the cell reference does not, inspect the source value for spaces or type differences.
Should I wrap every lookup formula in IFERROR?
Only after confirming that the underlying lookup is correct. Otherwise IFERROR can hide a real data or formula problem.

Related Excel workflows

Write an XLOOKUP Formula with ChatGPT Master XLOOKUP syntax, nested lookups, and multiple criteria. Clean Data in Excel with ChatGPT Fix formatting, remove duplicates, and standardise messy datasets. Fix #REF! Errors in Excel Identify broken references and reconstruct the correct formula. AI for Excel — All Workflows Browse every Excel workflow built for ChatGPT.