Finding Numbers from a Column Using Target Numbers

juken1

New Member
Joined
Jun 7, 2016
Messages
23
Hello,
Could I please have some excel help.
I'm looking for a formula to go into column H, I and J

Column H Formula: The formula I need to go into column H is using target numbers from Column F and G, use those target numbers to search Column D from top to bottom and whichever number is matched first (or if the "Target Higher" is matched first the number can go over or "Target Low: matched number can go lower)

Coumn I and J should be the time and date for the matched number.

For example purposes I've manually entered the results into the grey section (column H, I and J) to display what should be there if I can get a formula working.

[
mrexcel.xlsx
ABCDEFGHIJ
1NameDateTimeNumberNameTarget HighTarget LowTarget ReachedTarget DateTarget Time
2Steve1/01/202410:00:00 AM1Steve2.9132/01/202410:00:00 AM
3Steve1/01/20241:30:00 PM2John3224.8231/01/20241:30:00 PM
4Steve1/01/20242:00:00 PM1
5Steve2/01/202410:00:00 AM3
6Steve2/01/20241:30:00 PM4
7Steve2/01/20242:00:00 PM5
8Steve3/01/202410:00:00 AM3
9Steve3/01/20241:30:00 PM2
10Steve3/01/20242:00:00 PM1
11John1/01/202410:00:00 AM30
12John1/01/20241:30:00 PM23
13John1/01/20242:00:00 PM27
14John2/01/202410:00:00 AM22
15John2/01/20241:30:00 PM16
16John2/01/20242:00:00 PM19
17John3/01/202410:00:00 AM22
18John3/01/20241:30:00 PM21
19John3/01/20242:00:00 PM27
Data
 
hey cheers for your help with this Cubist, just one last thing can the J column have a column next to it saying "After Date" so i can have a specific date and time to start the search after to find numbers
 
Upvote 0

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Like this?
Book1
ABCDEFGHIJKLMNO
1NameTimeNumberDateNameTarget HighTarget LowAfter TimeAfter DateTarget DateTarget TimeTarget Reached
2Steve10:00:00 AM31/1/24Steve3110:05:00 AM1/1/241/1/241:30:00 PM1
3Steve1:30:00 PM11/1/24John32251:50:00 PM1/1/241/1/242:00:00 PM33
4Steve2:00:00 PM11/1/24
5Steve10:00:00 AM11/2/24
6Steve1:30:00 PM11/2/24
7Steve2:00:00 PM11/2/24
8Steve10:00:00 AM11/3/24
9Steve1:30:00 PM11/3/24
10Steve2:00:00 PM11/3/24
11John10:00:00 AM41/1/24
12John1:30:00 PM51/1/24
13John2:00:00 PM331/1/24
14John10:00:00 AM361/2/24
15John1:30:00 PM41/2/24
16John2:00:00 PM41/2/24
17John10:00:00 AM31/3/24
18John1:30:00 PM41/3/24
19John2:00:00 PM11/3/24
Sheet5
Cell Formulas
RangeFormula
M2:O3M2=XLOOKUP(1,($F$2:$F$19+$C$2:$C$19>=L2+K2)*($A$2:$A$19=H2)*(($D$2:$D$19>=I2)+($D$2:$D$19<=J2)),HSTACK($F$2:$F$19,$C$2:$D$19))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,882
Messages
6,175,164
Members
452,615
Latest member
bogeys2birdies

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