XLOOKUP not searching despite lookup value being present in worksheet

[jreid1]

So I have two worksheets in sheet 1 I am trying to perform a vlookup function that goes as follows XLOOKUP(B55,'Sheet2'!P:P,'Sheet2'!D:D) where the lookup value is 16/02/2023 00:00. I know this value is present in column P of sheet2 and that it has a corresponding value in column d but whenever I try to run this function I get the value is not available error. The lookup value is of the same format of the values in column P of sheet2 so i'm not sure what the problem is. Any help would be greatly appreciated thanks.

 

[awoohaw]

can you convert B55 AND Column P to general format and see if any of the datevalues have time portions?

 

[jreid1]

can you convert B55 AND Column P to general format and see if any of the datevalues have time portions?

Chaging it to general format just changes it to numbers like 44973

 

[awoohaw]

Yes,. and just do your lookup with those numbers into a column with those numbers.
Dates are actually numbers. The time portion is the decimal.
Here is an example:

mr excel questions 5.xlsm
	A	B	C
1	Date1	Date2	Are they equal:
2	02/16/2023	02/16/2023	FALSE
3	44973.28264	44973	FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C3	C2	=A2=B2

 

[awoohaw]

Or instead of changing the format to general. Change B55 an column P formats to mm/dd/yyyy hh:mm:ss, then try your lookups. The date and time must match if you are doing exact matching.

If not doing exact matching then column P needs to be in order.

 

[Fluff]

Chaging it to general format just changes it to numbers like 44973

Is that just B55 or is that all you get in col P as well? Or do you get values like 44973.0123 in col P?

 

[Alex Blakenburg]

If the below works then it is definitely a time issue.

=XLOOKUP(INT(B55),INT('Sheet2'!P:P),'Sheet2'!D:D)