How to fix a lookup when numbers are stored as text
Formula (Excel and Google Sheets)
=XLOOKUP(F1&"",A2:A8&"",B2:B8)Compare both sides as text: =XLOOKUP(F1&"",A2:A8&"",B2:B8). Joining "" turns a number into text, so 10457 matches "10457".
- Result on the example
- Tom Becker in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- 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
To a lookup, the number 10457 and the text "10457" are different values, so a plain lookup returns #N/A. &"" joins an empty string to a value, which always produces text. Doing it to both F1 and A2:A8 means the lookup compares text with text, whichever side was stored as what.
=XLOOKUP(F1&"",A2:A8&"",B2:B8)Result"Tom Becker"
F110457The value in F1.
F1&"""10457"The & operator joins as text.
A2:A87 rows × 1 col: "10452", "10453", "10455", "10457", …A range: 7 cells in one column.
A2:A8&""{7 rows × 1 col} "10452", "10453", "10455", "10457", …The & operator joins as text.
B2:B87 rows × 1 col: "Lena Park", "Omar Ruiz", "Noor Haddad", "Tom Becker", …A range: 7 cells in one column.
XLOOKUP(F1&"",A2:A8&"",B2:B8)"Tom Becker"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
F1&"""10457" - lookup_array
A2:A8&""{7 rows × 1 col} "10452", "10453", "10455", "10457", … - return_array
B2:B87 rows × 1 col: "Lena Park", "Omar Ruiz", "Noor Haddad", "Tom Becker", …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(F1&"",A2:A8&"",B2:B8). We ran it in Google Sheets mode on the same data and it returns Tom Becker.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(TEXT(F1,"0"),A2:C8,2,FALSE) | Tom Becker | Works in every version when the list holds text and the input is a number. |
| =VLOOKUP(F1,A2:C8,2,FALSE) | #N/ADifferent result | Shown for contrast: a number searched in a list of text. #N/A, even though 10457 is on the list. |
Common mistakes
- Numbers stored as text usually sit on the left of the cell and show a green triangle. ISTEXT tells you for sure:
=ISTEXT(A5). - If the list holds numbers and the input is text, convert the input instead:
XLOOKUP(--F1,A2:A8,B2:B8)orVALUE(F1). - Codes with leading zeros, like 00451, must stay text. Turning them into numbers drops the zeros.
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 a lookup when numbers are stored as text
F1 holds a real number. In F2, return the name for the employee number in F1.Questions
How do I convert the whole column to numbers?
Select it and use Data > Text to Columns > Finish, or multiply by 1 in a helper column and paste values.
Why does COUNTIF find it when VLOOKUP doesn't?
COUNTIF converts number-like text when comparing; VLOOKUP, XLOOKUP and MATCH don't.
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.