help return value date conditions

RICHHHHH

New member
Joined
Feb 22, 2022
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016 EXCEL
hello all,
kindly asking if anyone can help me with a formula for returning a value that meets some date conditions
many thanks

mar 1 20201%march 5 2020return cell B1 because cell C1 is on or after date in cell A1 and before date in cell A2
apr 1 20202%apr 10 2020return cell B2 because cell C2 is on or after date in cell A2 and before date in cell A3
may 1 20201.5%april 15 2020return cell B2 because cell C3 is on or after date in cell A2 and before date in cell A3
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,795
Reaction score
3
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
As there is no data in cells A4 and A5, this is not much use, sorry:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

A
B
C
D
1
Jan-20​
0.5%​
May-21​
return cell B4 because within range A1 to A5 AND C1 TO C5 date in cell C1 is on or after date in cell A4 but beofore date in cell A5
2
Mar-20​
1.0%​
Feb-20​
return cell B1 because within range A1 to A5 AND C1 TO C5 date in cell C2 is on or after date in cell A1 but beofore date in cell A2
3
Apr-20​
0.5%​
Jul-21​
return cell B4 because within range A1 to A5 AND C1 TO C5 date in cell C1 is on or after date in cell A4 but beofore date in cell A5
4
5
6
Sheet: Sheet1
 

RICHHHHH

New member
Joined
Feb 22, 2022
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016 EXCEL
apologies, please see an updated example
 

Attachments

  • testf.xlsx
    9.3 KB · Views: 4

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,795
Reaction score
3
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)
The formula refers to the range C1 to C5 as well - come on, please! How can we help when you are not giving us the full picture?

A guess:

=INDEX($B$1:$B$5,MATCH(C1,$A$1:$A$5,1))
 

RICHHHHH

New member
Joined
Feb 22, 2022
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016 EXCEL
thank you very much and again apologies for the bad sheet example. the formula works.
please could you tell whether the formula will always return the value from column B of the date in column A that is the nearest but earlier to the date in C1 and not the nearest but later to the date in C1?
 

AliGW

Administrator
Joined
Nov 8, 2015
Messages
1,795
Reaction score
3
Points
38
Location
Ipswich, Suffolk, England
Excel Version(s)
MS 365 (Beta Insider Channel)

RICHHHHH

New member
Joined
Feb 22, 2022
Messages
11
Reaction score
0
Points
0
Excel Version(s)
2016 EXCEL
thanks a lot for the explanation
 
Top