Forum Discussion
Dynamic lookup (Index?) function
Hello everyone,
my rows contain different KPIs and their values. The columns state the according month of the value entry per KPI (column B-M depict Jan22 to Dec22).
Via the LOOKUP function I was able to select the ongoing current value that was entered per row.
I'd like to know how I can calculate the current value (already foun it via lookup) minus the last value (the value before the current value).
Function as a question would be: What is the value before my latest entry in this row? (Excluding empty cells).
If that is only possible via VBA, pls provide the code and if possible one or two sentences with instructions.
Thank you all!
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)
11 Replies
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)
Excel version: Version 2202 Build 16.0.14931.20764 64 Bit
Somehow, I can't send a screenshot...
Vertical/rows: Different KPIs in the first column and the columns after are values regarding the KPI in the respective row and the date stated in the row above. So it is really just a calculation within one row.
