Forum Discussion

Copper Contributor

Mar 14, 2023

XLOOKUP when lookup value may be text string or number formatted as text

I have a data set that has lookup value which may be text string or number formatted as text, with the lookup range formatted General. How can I format the lookup value as general? I currently have...

excel

Formulas and Functions

Patrick2788

Silver Contributor

Mar 14, 2023

SallyC325

With lookup formulas, it's a matter of is the value text or a number. Also, the result of TEXT is going to be text.

XLOOKUP forcing the lookup value to be read as a number:

=XLOOKUP(A2*1,lookup,return)

XLOOKUP forcing a number to be treated as text:

=XLOOKUP(TEXT(A2,"0"),lookup,return)

SallyC325
Copper Contributor
Mar 15, 2023
Patrick2788Thanks for your response. This returns #VALUE for Text strings (e.g. CBYRH200020) when forcing XLOOKUP to be read as a number so still required an [if_not_found] alternative, if I have understood you correctly?

The lookup range has General format, so numbers look like numbers and text looks like text. Is there a way to force same formatting on the lookup value? I have tried to find the code for "General" format ... and failed.
- Patrick2788
  Silver Contributor
  Mar 15, 2023
  SallyC325
  You could use:
  =XLOOKUP(IFERROR(A2*1,A2),lookup,return)
  - Tyrus1366
    Copper Contributor
    Dec 04, 2024
    Great solution that I've been needing for some time! Thanks Patrick2788 !!