lookupValue Query - date/timestamp within a period

[gmazza76]

Good afternoon,

Apologies for this query if it has been covered, but I am having no look finding anything.

I have a formula that works for an exact time with other criteria, but I need to be able to check the date/time and pull the value from another column.
This sounds easy, but the source data is in 2 formats as follows, the issue seems to be milliseconds
Sheet 1 where I am pulling the value shows the date/time as "12/07/2021 09:01:43"
Sheet 2 where the data is aligning to shows the data/time as "12/07/2021 09:01:00"

I am used to writing a lookupvalue as below, but have no idea how to include a ">=" for just the time part as the date will always be the same. I am looking to include a 2 minute window due to connect between departments, but I am unsure how to dal with the milliseconds part

=LOOKUPVALUE(Sheet1[Description],Sheet1[matchCriteria],Sheet2[matchCriteria],Sheet1[date],sheet2[date],Sheet1[date],"<="time(sheet2[date]+(00,02,00)

All help appreciated.
thanks
Gavin

 

[gmazza76]

The formula works if I setup the date time in both sheets as "12/07/2021 09:01" and use the formula as is

=LOOKUPVALUE(Sheet1[Description],Sheet1[matchCriteria],Sheet2[matchCriteria],Sheet1[date],sheet2[date],Sheet1[date],sheet2[date]

Which will only match the exact matches, but when I try and add in the "<="sheet2[date]-time(00,02,00)
I get a format/value issue on the Sheet1[date]

Any ideas?