Nested if statement to look in between multiple date columns to see if tag is applicable based on 1 date

[lizfrinko]

Hi everyone,
I need help writing a nested if statement to solve for the "desired result" column. I need to look in between each of these dates and determine which was most recent to the "comparison date", and display the "desired result" tag.

The combinations on this are driving me crazy and I think there has to be a simple approach here. Do I write the if statement to look between all the date columns aside from the comparison date? Any help is appreciated, thank you.

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]DateA[/TD]
[TD="align: center"]DateATag[/TD]
[TD="align: center"]DateB[/TD]
[TD="align: center"]DateBTag[/TD]
[TD="align: center"]DateC[/TD]
[TD="align: center"]DateCTag[/TD]
[TD="align: center"]DateD[/TD]
[TD="align: center"]DateDTag[/TD]
[TD="align: center"]Comparison Date[/TD]
[TD="align: center"]Desired Result[/TD]
[/TR]
[TR]
[TD="align: center"]11/14/2017[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]3/27/2019[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]4/22/2019[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4/21/2019[/TD]
[TD="align: center"]CCC[/TD]
[/TR]
[TR]
[TD="align: center"]2/1/2018[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]4/29/2019[/TD]
[TD="align: center"]AAA[/TD]
[/TR]
[TR]
[TD="align: center"]3/1/2017[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]4/1/2017[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]5/1/2018[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]1/5/2015[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]4/29/2019[/TD]
[TD="align: center"]CCC[/TD]
[/TR]
[TR]
[TD="align: center"]11/1/2018[/TD]
[TD="align: center"]AAA[/TD]
[TD="align: center"]12/15/2018[/TD]
[TD="align: center"]BBB[/TD]
[TD="align: center"]1/5/2019[/TD]
[TD="align: center"]CCC[/TD]
[TD="align: center"]2/6/2019[/TD]
[TD="align: center"]DDD[/TD]
[TD="align: center"]4/29/2019[/TD]
[TD="align: center"]DDD[/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Welcome to the forums. Maybe try the below formula in Cell K2. Then just copy down the formula.

=INDEX(A2:H2,,MATCH(MAX(A2:H2),A2:H2,0)+1)