Formatting inconsistency breaks XLOOKUP - any formula-way to fix it?

[excelos]

Hello!

Often there is a mismatch in formatting so that XLOOKUP does not pick up looked-up values if not stored in the same format.

Is there a formula way to overcome this?

I was thinking to wrap the values in N() or TEXT() but I don't think these work.

Any idea?

Thanks!

 

[6StringJazzer]

XLOOKUP does not care about display format. I'm not sure I follow your terminology, because values are not stored in a format, they are displayed in a format.

There will be a difference if a value is text vs. numeric. For example, if you have the number 123 vs. the text string "123" XLOOKUP will not see those as a match, but that is data type, not display format.

Can you give a specific, concrete example where XLOOKUP does not work the way you are expecting?

Here is my example:

$scratch.xlsm
	A	B	C	D	E
1	Lookup table
2	Numbers	Target Values		Source value (Numbers)	Source value (Text)
3	123	A		456	456
4	456	B
5	789	C		XLOOKUP seeking a number	XLOOKUP seeking text
6				B	#N/A
Sheet10

Cell Formulas
Range	Formula
D6:E6	D6	=XLOOKUP(D3,$A$3:$A$5,$B$3:$B$5)

 

[excelos]

Thanks!

I am talking about both lookup values and lookup array to be numbers stored as numbers.
For some reason XLOOKUP did not recognise them until I converted both into 'stored as text'!

 

[Alex Blakenburg]

Ideally you want to clean up your data, as @6StringJazzer pointed out it is inconsistencies in the data that is the issue ie text on one side and numeric on the other side.

In MS 365 you can cheat and force it all to text which should overcome the issue without actually fixing the underlying data problem.
eg Using Jeff's example

=XLOOKUP(TRIM(D3),TRIM($A$3:$A$5),$B$3:$B$5)

There are other ways of converting it to Text but I find Trim to be nice and clear and covers up some other sins at the same time.

 

[6StringJazzer]

XLOOKUP did not recognise them until I converted both into 'stored as text'!

I am guessing that they were already text in one place. How were the numbers entered into your sheet? I have seen many cases where data is imported from or generated by another application, and what looks like numbers is actually text once it gets into Excel. You can use the ISTEXT function to diagnose this.