SOLVED

Dynamic lookup (Index?) function

Copper Contributor

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!

11 Replies

@sksk2424 

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?

Patrick2788_0-1668697201108.png

 

Also, if you're using LOOKUP, it suggests you might be on an older version of Excel. Which version do you have?

@Patrick2788 

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.

@Patrick2788 

 

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!

best response confirmed by sksk2424 (Copper Contributor)
Solution

@sksk2424 

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!

@sksk2424 

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))
Thanks! I am suggling with the column "50" in SEQUENCE.

Which number is the 50 refering to? The total amount of columns in my range?
How do I determine this number?

Thanks!!!
The 50 is allowing for potentially 50 columns. FILTER removes the blanks and then the subtraction is done between Last value and Second last value. This formula would be a bit more elegant with access to functions like TAKE and DROP.

@Patrick2788 

 

thank you for your reply!

 

Do you see something wrong with my function?

 

Spoiler
=LET(range,H5:AV30,r,XMATCH(TRUE,H:H,1)-1,c,XMATCH(TODAY(),H5:AV5,-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))

 

If I expand my selection for "range" and "c" a value returns. However, the value doesn't seem to be a calculation but more like an error within my formula.

 

Thanks in advance!!

@sksk2424 

The 50 is the problem. It may be best to pull the SEQUENCE number based on the rows in 'range':

=LET(range,C2:AQ27,r,XMATCH(TRUE,C:C,1)-1,c,XMATCH(TODAY(),C2:AQ2,-1),Current,INDEX(range,r,c),Last,INDEX(range,r,SEQUENCE(,ROWS(range))),f,FILTER(Last,Last>0),INDEX(f,1,COLUMNS(f))-INDEX(f,1,COLUMNS(f)-1))
Thank you!
1 best response

Accepted Solutions
best response confirmed by sksk2424 (Copper Contributor)
Solution

@sksk2424 

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)

View solution in original post