# How to fix a lookup when numbers are stored as text

Compare both sides as text: `=XLOOKUP(F1&"",A2:A8&"",B2:B8)`. Joining "" turns a number into text, so 10457 matches "10457".

**Excel:** `=XLOOKUP(F1&"",A2:A8&"",B2:B8)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Emp no. | Name | Department |  | Emp no. | 10457 |
| 10452 | Lena Park | Finance |  | Name |  |
| 10453 | Omar Ruiz | Sales |  |  |  |
| 10455 | Noor Haddad | IT |  |  |  |
| 10457 | Tom Becker | Sales |  |  |  |
| 10460 | Priya Shah | Marketing |  |  |  |
| 10461 | Ines Lopez | Finance |  |  |  |
| 10464 | Sam Okafor | IT |  |  |  |

Formula in F2: `=XLOOKUP(F1&"",A2:A8&"",B2:B8)`

Result: Tom Becker

## 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.

## Other ways

- `=VLOOKUP(TEXT(F1,"0"),A2:C8,2,FALSE)`: Works in every version when the list holds text and the input is a number.
- `=VLOOKUP(F1,A2:C8,2,FALSE)`: 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)` or `VALUE(F1)`.
- Codes with leading zeros, like 00451, must stay text. Turning them into numbers drops the zeros.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/lookup-numbers-stored-as-text