CODEWITHSUNDEEP
excel-formula
Sviatoslav Piliaiev
Sviatoslav Piliaiev

Reputation: 19

Way to find value in a date range Excell

I have Start date + time and End date + time in table 1, then i have a range of dates and values in table 2. what i want is to find the highest value from table 2 that happened in the date range from table 1. For example in table 1: start date 12/30/2020 08:03 ; end date 12/31/2020 17:26 in table 2 is: 12/30/2020 08:00 1145 12/30/2020 09:00 2145 12/30/2020 10:00 3912 12/30/2020 08:00 1472 and so on. so in the row of table 1 i want to see the number 3912

Upvotes: 0

Views: 65

Answers (2)

Terry W
Terry W

Reputation: 3257

Suppose both of your tables are structured reference table as shown below:

Table1

Table2

You can use either of the following formula to return the desired value:

=MAX((Table2[Date]>=Table1[@[Start Date]])*(Table2[Date]<=Table1[@[End Date]])*Table2[Value])

This one is using MAXIFS as suggested by Nicholas Hunter in the comment section:

=MAXIFS(Table2[Value],Table2[Date],">="&Table1[@[Start Date]],Table2[Date],"<="&Table1[@[End Date]])

Solutions

Upvotes: 1

Doragon
Doragon

Reputation: 367

Assuming table 1 = A1-B10 and table 2 is in D1-10,

try making another column (lets say F1-10) with the following: =IF(And(D1<B1,D1>A1),D1,0) and in whatever cell you want the highest Date, put =Large(F1-10)

Make sure that the final cell has "Date" format, otherwise it will look like a weird number. if you want to look at the intermediary steps, do the same for F1-10.

Upvotes: 0

Related Questions