Numbers stored as text stop lookups working — how to fix it

Why the type matters

Excel and Google Sheets keep a type for every cell. Text that happens to contain digits isn't converted automatically, and exact-match lookups don't convert between types either. So =XLOOKUP(1001, A2:A500, C2:C500) won't find a cell holding the text "1001", and the reverse fails too.

Text-numbers arrive in predictable ways:

• CSV and system exports where the ID column was quoted or the import treated it as text.
• Columns formatted as Text before data was typed or pasted in.
• Values typed with a leading apostrophe ('1001), which forces text.
• Formulas that return text: LEFT, MID, RIGHT, TEXT and concatenation with & always return text, even when the result is all digits.
• Google Sheets IMPORTRANGE or QUERY results whose source column was text.

How to spot them

• Green triangle: Excel flags a number stored as text with a small green triangle in the top-left corner and a warning icon offering Convert to Number (if background error checking is on).
• Alignment: with no alignment set, numbers sit to the right of the cell and text to the left.
• Status bar: select the column; if Sum is missing and only Count shows, the values are text.
• Formula check: =SUMPRODUCT(--ISTEXT(A2:A500)) counts the text cells in a column.

Convert text to numbers

Pick one method and apply it to the whole key column, then check again with ISNUMBER.

1. Convert to Number: select the flagged cells, click the warning icon, choose Convert to Number. Quick, but only appears when error checking flagged the cells.
2. Text to Columns: select the column, Data → Text to Columns → Finish. Excel re-parses each cell with the column's General format, turning digit text into numbers. Works on a whole column at once.
3. Formula: in a helper column, =VALUE(A2) or =--A2 (two minus signs), then paste as values.
4. Paste Special: type 1 in a spare cell, copy it, select the column, Paste Special → Multiply. Text-numbers are coerced to numbers.

If the column is formatted as Text, reformat it as General first, otherwise new entries arrive as text again. In Google Sheets, use Format → Number → Number, or =VALUE(A2) in a helper column.

Or convert the other side: when IDs have leading zeros

Converting "01002" to a number gives 1002 and loses the zero. If your IDs are codes rather than quantities — postcodes, account numbers, SKUs like 00450 — the right fix is to make both sides text. Convert the numeric side with a format that restores the padding:

=TEXT(B2, "00000")

Or convert inside the lookup, so you don't touch the source data. These two cover both directions in Microsoft 365 — the first turns the number into text, the second turns the text column into numbers:

=XLOOKUP(B2 & "", Customers!A2:A500, Customers!C2:C500, "not found")
=XLOOKUP(B2, --Customers!A2:A500, Customers!C2:C500, "not found")

The second form returns #VALUE! entries inside the array for any text that isn't numeric, which simply don't match. In older versions, use INDEX/MATCH with the same coercion and enter it as an array formula.

Power Query and SQL: set the type explicitly

In Power Query, set the key column's type on both queries before merging — both Text, or both Whole Number. A merge between a text column and a number column doesn't match anything, and Power Query won't warn you. Choose Text when leading zeros matter.

In SQL, cast both keys to the same type in the join:

SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c
  ON CAST(o.customer_id AS VARCHAR) = lpad(trim(c.customer_id), 5, '0');

That casts the numeric side to text and pads the text side to five digits so 1002 and "01002" line up — adjust the width to your IDs. DuckDB will try implicit casts in some comparisons, but being explicit avoids surprises. When Analistable loads a CSV it detects column types, so one file's ID may arrive as a number and the other's as text; the AI-written SQL casts keys when it joins, and you can see the cast in the query.

Worked example

Every lookup failed before; three match after, and the remaining miss is a customer genuinely absent from the CRM. Lookups that cross workbooks have extra traps — see XLOOKUP from another workbook — and the join spreadsheets guide covers choosing between a lookup and a join. For comparing functions, read VLOOKUP vs XLOOKUP vs INDEX MATCH.

Frequently asked questions

Why does VLOOKUP return #N/A when the number is clearly there?

One side holds the value as a number and the other as text. Check with ISTEXT and convert one side so both are the same type.

Why does Text to Columns fix numbers stored as text?

Finishing the wizard re-enters every cell under the column's format, so digit text formatted as General is parsed as a number.

How do I keep leading zeros and still match?

Keep the IDs as text and convert the numeric side with TEXT(B2, "00000") using as many zeros as the ID length.

Does Google Sheets have the same problem?

Yes. VLOOKUP and XLOOKUP in Sheets also treat 1001 and "1001" as different. Use VALUE or TO_TEXT to align the types.

Why do my converted numbers turn back into text?

The column is still formatted as Text, or the data is re-imported each time from a source that quotes the IDs. Change the column format to General and fix the type in the import step, such as a Power Query Changed Type step.

Related guides

• VLOOKUP vs XLOOKUP vs INDEX MATCH: which should you use?
• How to use XLOOKUP with another workbook
• Project hours vs budget template
• SEO report template for GA4 and Search Console
• Survey merge template