Extract value from last period with data

[BRISBANEBOB]

I am trying to extract the data from one of three different arrays. If there is a value in period 2016, use that. But if there is no value in 2016, go back to 2015. If there is a value there, use that. If there isn't, check 2014. If a value there, use that. If no value in any of the arrays, return zero.

I can do it with nested 'If' statements but I am looking for a more elegant solution.

I have attached a sample. Grateful for any ideas.

[FlameRetired]

This uses mostly brute force.

=IFERROR(IFERROR(IFERROR(1/(1/VLOOKUP(M9,$J$9:$K$18,2,0)),1/(1/VLOOKUP(M9,$G$9:$H$18,2,0))),1/(1/VLOOKUP(M9,$D$9:$E$18,2,0))),0)

[FlameRetired]

This is long. It can be made more robust with some modifications.

=INDEX(INDEX($D$9:$K$18,,MATCH(2,1/MMULT({1,1,1,1,1,1,1,1,1,1},(M9=$D$9:$J$18)*ISNUMBER($E$9:$K$18)),1)+1),MATCH(M9,INDEX($D$9:$K$18,,MATCH(2,1/MMULT({1,1,1,1,1,1,1,1,1,1},(M9=$D$9:$J$18)*ISNUMBER($E$9:$K$18)),1)),0))

[FDibbins]

Perhaps this?
=INDEX(D9:K9,MATCH(0,D9:K9,-1))
copied down

[BRISBANEBOB]

Thank you (again), works perfectly.

[FDibbins]

Happy to help and thanks for the feedback