XLOOKUP Error

[Jones1413]

Hello,

I have two sheets of data where I want to lookup a value using XLOOKUP. The first sheet is called July Hires and the second sheet, where I want to use the lookup array and lookup return value is called Sheet1.

I can't figure out why it is not returning the email address in cell B152 and is showing up as #N/A.

Please see screenshot attached.

 

[Joe4]

Probably because that number is entered as text on the one sheet (note the blue triangle in the upper right-hand corner) and is entered as a number on the other.
In other for the Lookup functions to work, you can only match text-to-text or number-to-number, never text-to-number.
So you will need to fix your data so they are both the same data type.

 

[CluaseTony]

You could try formula +ABS(A152) this converts a text number to a number, the reverse of that is =Text(A152,"00000000") the number of noughts is how ever long the string is!