Forum Discussion
Solved
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...
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)
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!!!
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.
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!!
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))