Get value of nth last cell in range (may have blanks)

[Steven101]

Hi, I have a range (G3:Z3) where the cells will always be filled starting from column G. I have been able to get the last completed cell in range using;

=INDEX(G3:Z3,MATCH(9.99999999999999E+307,G3:Z3))

what I'm looking for is the 2nd last, 3rd last completed cell in the range.

Is this possible?

Thanks, Steven

 

[steve the fish]

This seems to work fine:

=INDEX(G3:Z3,LARGE(INDEX(((G3:Z3<>"")*(COLUMN(G3:Z3))-COLUMN($G$3)+1),0),2))

The 2 at the end means it picks up the 2nd last.

 

[Scott T]

Excel 2010

	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
3			1	2	3		5	6	7	8	9	10	11	12	13	14	15		17	18		20
4
5	last	20
6	2nd to last	18
7	3rd to last	17
8		15
9		14
10		13
11		12
12		11
13		10
14		9
15		8
16		7
17		6
18		5
19		3
20		2
21		1
22
23
24

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet1​

Copy the formula down to get the 2nd, 3rd.... to last

Array Formulas Cell Formula F5 {=IF(ROWS($F$5:F5)>COUNTA($G$3:$Z$3),"",INDEX($G$3:$Z$3,LARGE(IF($G$3:$Z$3<>"",COLUMN($G$3:$Z$3)-COLUMN($G$3)+1),ROWS($F$5:F5))))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Steven101]

Thanks steve the fish...many thanks