XLOOKUP cannot match a number stored as text

Reproduce the mismatch

A2 contains the text identifier 00123; C2 is the number 123. D2 checks the storage types before lookup and displays “Type mismatch”. The visible digits are related, but the values have different types and the leading zeros may be meaningful.

=IF(AND(ISTEXT(A2),ISNUMBER(C2)),"Type mismatch",XLOOKUP(C2,$A$2:$A$4,$B$2:$B$4,"No match"))

Choose a repair that respects the IDs

If every ID uses five digits by policy, E2 formats the number as text before matching:

=XLOOKUP(TEXT(C2,"00000"),$A$2:$A$4,$B$2:$B$4,"No match")

The expected result is Part A. The number-based alternative first converts A2:A4 into numeric helpers G2:G4 with =VALUE(A2), filled down. F2 uses =XLOOKUP(C2,$G$2:$G$4,$B$2:$B$4,"No match"). It finds Part A; F3 and F4 independently return Part B and Part C. However, 00123 and 123 become the same numeric ID; do not use this route when leading zeros distinguish records. In row 3, the text ID 456 matches numeric lookup 456 after a text or numeric repair.

Use ISNUMBER and ISTEXT on the actual cells before changing a source column. Microsoft also lists number/text mismatches among lookup #N/A causes. Converting an ID column to Number may permanently discard leading zeros. For several conditions at once, use the separate multiple-criteria lookup.