Dates - Grabbing the right one

[torourke17]

I'm trying to make cell O9 the 'most recent event date' 'as of' a certain date, where the 'most recent event date' can't be more recent than the 'as of' date. The 'as of' date could be current or historical. Trying to get 1 formula to rule them all. Scenarios below:

Existing Formula in cell O9
=IF(IF(OR(N9>=$E$1,N9=""),H9,N9)>$E$1,"",IF(N9="",H9,N9))

Scenario 1 (My Formula DOES NOT Work Right and returns Event Date 2, instead of Event Date 1)
As of Date: 12/27/2019 (cell E1)
Event Date 1: 12/19/2019 (cell H9)
Event Date 2: 2/12/2020 (cell N9)
Most Recent Event Date: Cell O9 should be 12/19/2019, because 2/12/2020 is greater than 12/27/2019. It is returning 2/12/2020)

Scenario 2 (My Formula Works Right)
As of Date: 2/26/2020
Event Date 1: 12/19/2019 (cell H9)
Event Date 2: 2/12/2020 (cell N9)
Most Recent Event Date: Cell O9 should be 2/12/2020, because it isn't greater than 2/26/20

Scenario 3 (My Formula Works Right)
As of Date: 2/26/2020
Event Date 1: 12/19/2019 (cell H9)
Event Date 2: Null/Blank
Most Recent Event Date: Cell O9 should be 12/19/2019, because there is no Event Date 2

 

[jasonb75]

Try

=IFERROR(LOOKUP(E1,CHOOSE({1,2},IF(H9<>"",H9),IF(N9<>"",N9))),"")