Get last 2 non blank values of a row

[Dani_LobP]

Hi,

I am trying to make a column to obtain last values that were added in some table. Each month table expands so new values are added...

So far, I managed to get the way to obtain last value, last value -1 and last value -2 (so I'm having last 3 values in other words).

My problem comes when the last values happen to have same value. then formula seems that cant read it as 2 separate items and ignores it.
And for some reason not always reads correctly values.

Below example table (I actually work with values than can be also text, or %, not just numbers, but I assume formula would work same regardless the cell format).

Last value minus2	Last value minus 1	Last value	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
#VALUE!	#SPILL!	1	1	2	4	2	1
#SPILL!	0	1	0	1	1	2	1
1	3	5	1	3	5	5
#VALUE!	#SPILL!	2	2	4	1	2
#VALUE!	#SPILL!	2	2	3	2	2
1	1	2	1	1	1	2

A2 formula: =INDEX(D2:O2,MATCH(LOOKUP(2,1/(D2:O2<>""),D2:O2),D2:O2,0)-2)
B2 formula: =INDEX(D2:O2,MATCH(LOOKUP(2,1/(D2:O2<>""),D2:O2),D2:O2,0)-1)
C2 formula: =IFNA(LOOKUP(2,1/(D2:O2<>""),D2:O2),"")

Thanks in advance!

 

[Dani_LobP]

This cheats slightly but will work how you have it setup there. In A2:

=LOOKUP(2,1/($D2:$O2<>""),B2:M2)

Drag across and down.

what would be in the case that there is missing values in columns and just have 2? any "cheat" for that? :D
above shared screen and idea, but not fully working :D

 

[Fluff]

How about in T3 dragged down only

++Fluff.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1				Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr
2
3		1	2	1	2
4	1	5	10	1				5				10
5	13	14	15	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
6			8				8
Sheet4

Cell Formulas
Range	Formula
Y3:AK3,AA6:AK6,W6:Y6,AB4:AD4,X4:Z4	Y3	=""
T3:V6	T3	=LET(f,FILTER(W3:AK3,W3:AK3<>"",""),s,SEQUENCE(,3,COLUMNS(f)-2),IF(s<1,"",INDEX(f,s)))
Dynamic array formulas.

 

[Dani_LobP]

How about in T3 dragged down only

++Fluff.xlsm
	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1				Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr
2
3		1	2	1	2
4	1	5	10	1				5				10
5	13	14	15	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15
6			8				8
Sheet4

Cell Formulas
Range	Formula
Y3:AK3,AA6:AK6,W6:Y6,AB4:AD4,X4:Z4	Y3	=""
T3:V6	T3	=LET(f,FILTER(W3:AK3,W3:AK3<>"",""),s,SEQUENCE(,3,COLUMNS(f)-2),IF(s<1,"",INDEX(f,s)))
Dynamic array formulas.

that one seems to do the drill! thanks a lot!
ill keep testing and let you know if something. but looks good so far

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Dani_LobP]

Hi,

Just found out some flaw in what i have built. Not sure where is supposed to be a mistake but i cant seem to see it.
I have the below table (example):

And from this table, i build the below:

For last values minus 1 and minus 2 i am using formula provided above (marked as solution and working great), and for the Month columns I am using the below (which happens to have some error:

Q3: =INDEX($W$1:$BG$1,MATCH(LOOKUP(2,1/(W3:BG3<>""),$W$1:$BG$1),$W$1$BG$1,0)-1)
R3: =INDEX($W$1:$BG$1,MATCH(LOOKUP(2,1/(W3:BG3<>""),$W$1:$BG$1),$W$1:$BG$1,0)-2)
S3: =INDEX($W$1:$BG$1,MATCH(LOOKUP(2,1/(W3:BG3<>""),$W$1:$BG$1),$W$1:$BG$1,0))

As you can see in pic2, it should not be displaying JUL1 (Q) and JUN (R), but MAY (Q) and APR (R) ... since is where next values are.

Thanks in advance.

 

[Fluff]

How about

=LET(f,FILTER(W$1:BG$1,W3:BG3<>"",""),s,SEQUENCE(,3,COLUMNS(f)-2),IF(s<1,"",INDEX(f,s)))

 

[Dani_LobP]

How about

=LET(f,FILTER(W$1:BG$1,W3:BG3<>"",""),s,SEQUENCE(,3,COLUMNS(f)-2),IF(s<1,"",INDEX(f,s)))

Thanks for your quick reply Fluff! as always very helpful.

If you happen to have some free time and feel like explaining this formula id be very grateful. Is already 2nd time i am using this one you shared and wonder if you can let know what each part of it is doing.
Either way, thanks a lot again!

 

[Fluff]

The filter function returns all the headers where row 3 is not blank & stores that in the variable f.
The sequence returns 3 numbers starting with the number of columns in f less 2 & the index then retunr those 3 column headers.