Hi,

It's probably easier than I think but I'm stucked with this formula to get an average of the value if the daily ref. exist in the matrix on any of the days before.

IF B19 (light blue cell) exist in G:L (red cells) any day it should return the value from that day F2:18 (yellow cells) and calculate an average in the green cell.

Note 1. Value in matrix are uniqe on row-level which means C20C (Cell B19) only exist maximum once per row between cell G:L.

Note 2. Date will grow. Today we found C20C (Cell B19) in Matrix J16 and H4. In future it might appear in +100 cells. Every new date should calculate an average based on all days before this date (if the reference appear in the matrix).

Hope it is clear what I try to do.


Excel.jpg