Prioritise a list and give a date output depending on this

[Razaster]

Hi,

I'm trying to create a formula for the attached that will prioritise based on the information with cells - ideally it will look at cells I, G, E, C for "surveyed" in that order and if surveyed is present it will populate from the next cells date (J, H, F, D)

[Glenn Kennedy]

What is the expected result for Row 3? And why?

[Roel Jongman]

I think a nested IF is the most efficient in this case.. because of the condition of stepping backwards thru the columns to find the date. with status surveyed
Try this formula.

Please Login or Register  to view this content.

[FlameRetired]

I seem to have interpreted differently. Please try in L2 and fill down.

Please Login or Register  to view this content.