XLOOKUP, Finding a Value based on Time and Date

J

juken1

New Member
Joined
Jun 7, 2016
Messages
23
Hello
I'm looking for a formula I can fill down column I with

The formula I need should find a value in the C column, by matching the F and D column but after the Time (G column) and on the same date (H column)

The result should I2 should be 0.265
and I3 0.55

MergedStockData_05_07_2024_144528.xlsx
ABCDEFGHIJ
1Number1TimeValue1NameDateNameTime DateValue1
22024010810:00:00 AM0.27TESTMonday, 08 Jan 2024TEST10:16 AMMonday, 8 January 20240.265< Need Formula here.Example Result
32024010810:15:00 AM0.275TESTMonday, 08 Jan 2024TEST21:33 PMSaturday, 06 Apr 20240.55< Need Formula here.Example Result
42024010810:30:00 AM0.265TESTMonday, 08 Jan 2024
52024010810:45:00 AM0.265TESTMonday, 08 Jan 2024
62024010911:00:00 AM0.265TESTTuesday, 09 Jan 2024
72024010911:15:00 AM0.265TESTTuesday, 09 Jan 2024
82024010911:30:00 AM0.265TESTTuesday, 09 Jan 2024
92024010911:45:00 AM0.27TESTTuesday, 09 Jan 2024
10202403712:00:00 PM0.27TEST2Saturday, 06 Apr 2024
11202403712:15:00 PM0.27TEST2Saturday, 06 Apr 2024
12202403712:30:00 PM0.27TEST2Saturday, 06 Apr 2024
13202403712:45:00 PM0.265TEST2Saturday, 06 Apr 2024
1420240371:00:00 PM0.265TEST2Saturday, 06 Apr 2024
1520240371:15:00 PM0.265TEST2Saturday, 06 Apr 2024
1620240371:30:00 PM0.265TEST2Saturday, 06 Apr 2024
1720240371:45:00 PM0.55TEST2Saturday, 06 Apr 2024
1820240372:00:00 PM0.265TEST2Saturday, 06 Apr 2024
Simulator
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
like this ?

Excel Formula: 
=LET(
timetrigger,G2,datetrigger,H2,nametrigger,F2,
names,$D$2:$D$18,times,$B$2:$B$18,value,$C$2:$C$18,dates,$E$2:$E$18,
data,FILTER(HSTACK(times,value),(dates=datetrigger)*(names=nametrigger)),
XLOOKUP(timetrigger,CHOOSECOLS(data,1),CHOOSECOLS(data,2),"Not Found",1))

1720168330988.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Find 1st instance in column A, paste corresponding value from column B into another cell and continue down until end of data
Replies
2
Views
83
MrsFraser07
M
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
89
cooleomter
C
tezza
  • Solved
Autosum specific rows and columns
Replies
2
Views
274
tezza
tezza

Forum statistics

Threads
1,224,811
Messages
6,181,081
Members
453,021
Latest member
Justyna P

Share this page

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