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!

 

[steve the fish]

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.

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last value minus2	Last value minus 1	Last value	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	4	2	1	1	2	4	2	1
3	1	2	1	0	1	1	2	1
4	3	5	5	1	3	5	5
5	4	1	2	2	4	1	2
6	3	2	2	2	3	2	2
7	1	1	2	1	1	1	2
Sheet6

Cell Formulas
Range	Formula
A2:C7	A2	=INDEX($D2:$O2,AGGREGATE(14,6,(COLUMN($D2:$O2)-3)/($D2:$O2<>""),4-COLUMN()))

 

[Dani_LobP]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last value minus2	Last value minus 1	Last value	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	4	2	1	1	2	4	2	1
3	1	2	1	0	1	1	2	1
4	3	5	5	1	3	5	5
5	4	1	2	2	4	1	2
6	3	2	2	2	3	2	2
7	1	1	2	1	1	1	2
Sheet6

Cell Formulas
Range	Formula
A2:C7	A2	=INDEX($D2:$O2,AGGREGATE(14,6,(COLUMN($D2:$O2)-3)/($D2:$O2<>""),4-COLUMN()))

Thanks, this seems to work as I want :D

 

[Dani_LobP]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last value minus2	Last value minus 1	Last value	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	4	2	1	1	2	4	2	1
3	1	2	1	0	1	1	2	1
4	3	5	5	1	3	5	5
5	4	1	2	2	4	1	2
6	3	2	2	2	3	2	2
7	1	1	2	1	1	1	2
Sheet6

Cell Formulas
Range	Formula
A2:C7	A2	=INDEX($D2:$O2,AGGREGATE(14,6,(COLUMN($D2:$O2)-3)/($D2:$O2<>""),4-COLUMN()))

Actually, i just noticed that it won't work in the case that there is a formula in the cell.

All the empty cells in the row, have this formula: =IF(Template!B3="","",Template!B3) , so wherever you see empty cell in the columns, actually there is a formula, and when the original table is populated, then the value will be displayed here.

So the formula you helped with, works, in the case there is nothing in the cell, that's why I had the condition of being cell other than "" .
Somewhere in your formula should be able to add IF(D2:O2<>"") maybe? , as I had in my example. Im trying to play with that, but so far no luck :D

Makes sense what I mentioned? thanks.

 

[bebo021999]

In my mini sheet, cells with red triangle comment, are formula : = ""
cell is blank, or cell with fornula that generate "", that's no matter , with my formula.
If its your matter, could you post your sheet, with error ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last value minus2	Last value minus 1	Last value	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	2	1	5	1	2	4	2	1		5
3	1	2	1	0	1		2	1
4	3	5	5	1	3	5	5
5	4	1	2	2	4	1	2
6	3	2	2	2	3	2	2
7	1	1	2	1	1	1	2
Sheet2

Cell Formulas
Range	Formula
A2:C7	A2	=INDEX($D2:$O2,AGGREGATE(14,6,(COLUMN($D2:$O2)-3)/($D2:$O2<>""),4-COLUMN()))
I2,I4,F3	I2	=""

 

[Dani_LobP]

In my mini sheet, cells with red triangle comment, are formula : = ""
cell is blank, or cell with fornula that generate "", that's no matter , with my formula.
If its your matter, could you post your sheet, with error ?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Last value minus2	Last value minus 1	Last value	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	2	1	5	1	2	4	2	1		5
3	1	2	1	0	1		2	1
4	3	5	5	1	3	5	5
5	4	1	2	2	4	1	2
6	3	2	2	2	3	2	2
7	1	1	2	1	1	1	2
Sheet2

Cell Formulas
Range	Formula
A2:C7	A2	=INDEX($D2:$O2,AGGREGATE(14,6,(COLUMN($D2:$O2)-3)/($D2:$O2<>""),4-COLUMN()))
I2,I4,F3	I2	=""

I actually have my columns from W to AK ... Data for Feb is actually in W3, and last column would be AK3.
I am unsure if that would be any reason for formula to not adapt if I modify the D2:O2 into W3:AK3 ...

I get some #Num! error when i do so.

Hopefully this helps. thanks!

 

[Fluff]

Have you tried the formula from Steve in post#2?

 

[Dani_LobP]

Yes, but only issue I see is when the data is missing and only have 2 values, in that case it seems to be repeating again one.
Ideally should be displaying some error or Not applicable or something. have a number could lead to confusion.

apart from that, seems to be working good.

 

[Dani_LobP]

Yes, but only issue I see is when the data is missing and only have 2 values, in that case it seems to be repeating again one.
Ideally should be displaying some error or Not applicable or something. have a number could lead to confusion.

View attachment 65414

apart from that, seems to be working good.

Trying to see if this would fix/solve the case: =IF(LOOKUP(2,1/($W3:$AK3<>""),U3:AK3)=V3,"",LOOKUP(2,1/($W3:$AK3<>""),U3:AK3))

But not sure if can really use it since in the case as per below, when for some reason numbers are same, would show as empty..