Determine location prior to comparing dates in Excel

0
votes

I am trying to write a formula to compare a date against two dates but I need to determine the location of those two dates prior to comparison.

Image of how the table looks

By way of example, I have dates in a table as below. 

    A        B  C           D
1   FY     QTR  Start date  End Date
2   2019    Q1  2/4/2018    5/5/2018
3   2019    Q2  5/6/2018    8/4/2018
4   2019    Q3  8/5/2018    11/3/2018
5   2019    Q4  11/4/2018   1/2/2019

I have the quarter I am working in cell B9. I want to determine if the date in cell H6 falls between start date and end date based on the quarter value in cell B9.

1
excel-formula

1 Answers

0
votes

Test if H6 is >= Start date AND <= End Date

Assuming dates are entered as DateTime Serial Numbers (and formated as dates) use

=AND($H$6 >= VLOOKUP($B$9,$B$1:$D$5,2,0), $H$6 <= VLOOKUP($B$9,$B$1:$D$5,3,0))