VLOOKUP and MAXIF multiple dates based on a value - Need Help

[Renold35]

I need help in creating a formula that would VLOOKUP a specific value e.g., Item A, Item B, and Item C and return the max value of Baseline and Revised Start Dates for each Item A,B, and C separately in a different worksheet.

The goal is to return the latest date or revised date, or if revised date is blank, return the baseline date. Also, would like a conditional formula that detects errors such that revised date cannot come before baseline start data and so on.

Formula Worksheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Items[/TD]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Check[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10-1[/TD]
[TD]12-31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]11-1[/TD]
[TD]11-15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Check[/TD]
[TD]2-15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Reference Worksheet


[TABLE="width: 500"]
<tbody>[TR]
[TD]Items[/TD]
[TD]Base Start Date[/TD]
[TD]Base End Date[/TD]
[TD]Revised Start Date[/TD]
[TD] Revised End Date[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10-1[/TD]
[TD]11-15[/TD]
[TD]-[/TD]
[TD]12-31[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]10-[/TD]
[TD]11-15[/TD]
[TD]11-1[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]11-1[/TD]
[TD]12-31[/TD]
[TD]10-31[/TD]
[TD]2-15-18[/TD]
[/TR]
</tbody>[/TABLE]

 

[Zubair Muhammad]

Hi Renold

See if this helps

https://1drv.ms/x/s!AhiuKaBxxAufji2Ryrtaaz6wlo1a

Formula is too long so I am attaching the file

 

[Renold35]

Can you please re-attach the file? It doesn't work.

 

[Zubair Muhammad]

Here it goes

http://1drv.ms/x/s!AhiuKaBxxAufji2Ryrtaaz6wlo1a