SOLVED

# Dynamic lookup (Index?) function

Occasional Contributor

# 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!

11 Replies

# Re: Dynamic lookup (Index?) function

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?

# Re: Dynamic lookup (Index?) function

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.

# Re: Dynamic lookup (Index?) function

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 (Occasional Contributor)
Solution

# Re: 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)``

# Re: Dynamic lookup (Index?) function

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!

# Re: Dynamic lookup (Index?) function

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))``

# Re: Dynamic lookup (Index?) function

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!!!

# Re: Dynamic lookup (Index?) function

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.

# Re: Dynamic lookup (Index?) function

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.

``=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))``