Forum Discussion
Dynamic lookup (Index?) function
This may work for you:
=LET(range,A1:M26,r,XMATCH(TRUE,A:A,1)-1,c,XMATCH(TODAY(),A1:Z1,1),Current,INDEX(range,r,c),Previous,INDEX(range,r,c-1),Current-Previous)
To provide a solution for this request, we're going to need to know what your vertical intersection looks like. The months run left-to-right in columns. Do you have numbers representing days of the month running vertical like this?
Also, if you're using LOOKUP, it suggests you might be on an older version of Excel. Which version do you have?
According to your screenshot:
Imagine your days would be KPIs and I would like to know what the delta between your current value of KPI 17 and the value before the current (in this case629).
Remember it has to be dynamic due to the fact that there wont be forecasts so the latest value always equals the current value and there can be empty cells in a row if the KPI is tracked monthly, quarterly etc.
Thanks!
This may work for you:
=LET(range,A1:M26,r,XMATCH(TRUE,A:A,1)-1,c,XMATCH(TODAY(),A1:Z1,1),Current,INDEX(range,r,c),Previous,INDEX(range,r,c-1),Current-Previous)Thank you so much Patrick2788
Works perfectly if there are no empty cells.
Can you think of a solution if for example the value 629 doesn't exist=empty cell?
The ideal return would therefore be: 35 - 845 = -810
Basically, ignoring the empty cell and going with the next lower entered value.
My current solution would be:
Previous,IF((INDEX(range,r,c-1)=0),INDEX(range,r,c-2),(INDEX(range,r,c-1))
Unfortunately, I would need to extend the If clauses up to 11 blank cells because that is the maximum number of blank cells that could occur.
Is there a smarter way?
Thank you!
Try this one:
=LET(range,A1:BA26,r,XMATCH(TRUE,A:A,1)-1,c,XMATCH(TODAY(),A1:AA1,1),Current,INDEX(range,r,c),Last,INDEX(range,r,SEQUENCE(,50)),f,FILTER(Last,Last>0),INDEX(f,1,COLUMNS(f))-INDEX(f,1,COLUMNS(f)-1))
