Spreadsheet Practice

How to fix VLOOKUP #N/A with numbers stored as text

Formula (Excel and Google Sheets)

=VLOOKUP(F1&"",A2:C7,2,FALSE)

Make both sides the same type. When the table holds text IDs, turn the lookup number into text: =VLOOKUP(F1&"",A2:C7,2,FALSE).

Result on the example
Noor Haddad in F2
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

The IDs in column A are text, while F1 holds the number 10433. A lookup never treats the number 10433 and the text "10433" as equal, so it returns #N/A. Joining an empty string, F1&"", turns the number into text, and the match works.

=VLOOKUP(F1&"",A2:C7,2,FALSE)

Result"Noor Haddad"

  1. F110433

    The value in F1.

  2. F1&"""10433"

    The & operator joins as text.

  3. A2:C76 rows × 3 cols: "10421", "Lena Park", "Finance", "10427", …

    A range: 6 rows × 3 columns.

  4. VLOOKUP(F1&"",A2:C7,2,FALSE)"Noor Haddad"

    VLOOKUP: Finds a value in the first column and returns a value from the same row. Use FALSE for exact.

    • lookup_valueF1&"""10433"
    • table_arrayA2:C76 rows × 3 cols: "10421", "Lena Park", "Finance", "10427", …
    • col_index_num2
    • range_lookupFALSE

In Google Sheets

The same formula works in Google Sheets: =VLOOKUP(F1&"",A2:C7,2,FALSE). We ran it in Google Sheets mode on the same data and it returns Noor Haddad.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(F1,--A2:A7,B2:B7)Noor HaddadConverts the ID column to numbers instead. Google Sheets needs ARRAYFORMULA around it.
=VLOOKUP(TEXT(F1,"0"),A2:C7,2,FALSE)Noor HaddadTEXT does the same conversion with a format, which also handles leading zeros.

Common mistakes

  • The opposite case, numbers in the table and text in the lookup cell, needs the reverse fix: =VLOOKUP(--F1,A2:C7,2,FALSE).
  • Formatting a column as Number doesn't convert text already in it. Use Data, Text to Columns, Finish, or the warning icon's Convert to Number.
  • Text numbers sit on the left of the cell by default, often with a small green triangle.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Fix VLOOKUP #N/A with numbers stored as text

In F2, return the name for the employee ID in F1. The IDs in column A were imported as text.

Questions

How do I check which side is text?

=ISTEXT(A4) and =ISTEXT(F1). The lookup works only when both give the same answer.

Does XLOOKUP have the same problem?

Yes. XLOOKUP, MATCH and INDEX/MATCH all treat 10433 and "10433" as different values.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.