#N/A using Vlookup when value exists

When VLOOKUP returns #N/A even though the value appears to exist, the cause is usually one of these issues:

1. The lookup value is not an exact match
   • If range_lookup is FALSE (exact match), #N/A means the exact value is not found in the first column of the table_array.
   • Common reasons:
     • Extra spaces before/after the value in either the lookup cell or the table.
     • One is stored as text and the other as a number.
     • Hidden characters or different formatting.
2. The lookup value is outside the range when using approximate match
   • If range_lookup is TRUE or omitted, and the lookup value is smaller than the smallest value in the first column of the table_array, VLOOKUP returns #N/A.
   • In this mode, the first column must be sorted alphabetically or numerically; otherwise results can be unexpected.

To troubleshoot:

1. Check the range_lookup argument
   • Use FALSE for exact match: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE).
2. Clean the data
   • Remove leading/trailing spaces and ensure both lookup_value and first column of table_array are the same type (both text or both numbers).
3. Confirm the lookup column
   • Ensure the value really exists in the first column of the table_array range used in VLOOKUP.

If the #N/A is expected (for example, when the value truly does not exist), wrap the formula in an error handler to control the output, such as:

• =IFERROR(VLOOKUP(...), "Not found") to handle any error, or
• =IFNA(VLOOKUP(...), "Not found") to handle only #N/A specifically.

The IFNA function returns the value specified if the formula returns #N/A; otherwise it returns the formula result.

References:

• How to correct a #N/A error
• VLOOKUP function
• Quick Reference Card: VLOOKUP troubleshooting tips
• IFNA function