Forum Discussion
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...
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)
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.
Great solution that I've been needing for some time! Thanks Patrick2788 !!
