XLOOKUP not searching despite lookup value being present in worksheet

jreid1

New Member
Joined
Sep 28, 2022
Messages
4
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
Platform
  1. Windows
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
can you convert B55 AND Column P to general format and see if any of the datevalues have time portions?
 
Upvote 0
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
 
Upvote 0
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
ABC
1Date1Date2Are they equal:
202/16/202302/16/2023FALSE
344973.2826444973FALSE
Sheet1
Cell Formulas
RangeFormula
C2:C3C2=A2=B2
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
If the below works then it is definitely a time issue.
Excel Formula:
=XLOOKUP(INT(B55),INT('Sheet2'!P:P),'Sheet2'!D:D)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top