Lookup maximum value with additional maximum criteria

[nabaron]

Hello!

I am trying to populate missing values based on historical data. The issue is that not every historical year contains data for every variable. So, what I need is a formula to pull the maximum (non-zero) value for a variable from the most recent year that contains data.

Example historical data can be seen below...

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Year[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X
[/TD]
[TD]2014[/TD]
[TD]330[/TD]
[TD]4325[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2014[/TD]
[TD]250[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2015[/TD]
[TD]290[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2015[/TD]
[TD]330[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2016[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]2016[/TD]
[TD]400[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2015[/TD]
[TD]0[/TD]
[TD]3200[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2015[/TD]
[TD]230[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2016[/TD]
[TD]200[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]2016[/TD]
[TD]175[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


Based on the above table, I would want to return the following results for variables A and B for each item X and Y...

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Item[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD]400[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD]200[/TD]
[TD]4000[/TD]
[/TR]
</tbody>[/TABLE]


Appreciate any and all help!!

 

[oldbrewer]

[TABLE="width: 844"]
<colgroup><col><col span="6"><col><col><col><col></colgroup><tbody>[TR]
[TD]I am trying to populate missing values based on historical data. The issue is that not every historical year contains data for every variable. So, what I need is a formula to pull the maximum (non-zero) value for a variable from the most recent year that contains data.[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Year[/TD]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Example historical data can be seen below...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of A[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]Year[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]4325[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]2014[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]4500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3200[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]230[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]2016[/TD]
[TD="align: right"]175[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Year[/TD]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]return the following results for variables A and B for each item X and Y...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of A[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Item[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]4500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]290[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]4000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]330[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]BY RUNNING A SERIES OF PIVOT TABLES[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]you can take the last table with numbers in it[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]and take the max of those numbers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Year[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Item[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]clunky but I can't see any other way as I run excel 2000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]and do not have sumifs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Count of A[/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]400[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Grand Total[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]