Lookup function (subitems)

Hello everyone,

I’m looking for a lookup function alternative for my subitems.

I have created a KPI dashboard with two different kind of KPI items. Each Item can be either a “revenue” or “score”.
For each item I have created subitems with the following columns: months (jan – dec), item type (revenue or score), Actual YTD and End Year. Subitems sum of Actual YTD and End Year are mirrored to the parent item.

For “revenue” the Actual YTD is quite easy. I just use the formula IF({Type}=“Revenue”,SUM({all months…)))

However, I also want to create a IF({Type}=”Score” condition for Actual YTD where want to use a lookup function to display the latest score from a month column. The Score type is not accumulated over year, so I just want to show the latest value from month columns.

Any suggestions?