Get last but one non blank cell

[Kulsumbi]

Hello

I want to pull last but one non blank blan k cell

Example:

À B C D
1 2 3
5 6 7 8

I need to pull 2 and 7 that is last but one non blank cell.

Please assist

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you use something like this?

20 10 04.xlsm
	A	B	C	D	E	F	G	H
1	1	2	3					2
2	5	6	7	8				7
3	2							N/A
4	2	5		9		5		9
@nd Last

Cell Formulas
Range	Formula
H1:H4	H1	=IFERROR(INDEX(A1:F1,AGGREGATE(14,6,(COLUMN(A1:F1)-COLUMN(A1)+1)/(A1:F1<>""),2)),"N/A")

 

[Kulsumbi]

Welcome to the MrExcel board!

Could you use something like this?

20 10 04.xlsm
	A	B	C	D	E	F	G	H
1	1	2	3					2
2	5	6	7	8				7
3	2							N/A
4	2	5		9		5		9
@nd Last

Cell Formulas
Range	Formula
H1:H4	H1	=IFERROR(INDEX(A1:F1,AGGREGATE(14,6,(COLUMN(A1:F1)-COLUMN(A1)+1)/(A1:F1<>""),2)),"N/A")

It works fine ..!! Thanks

If you don't mind.. can you just elaborate the formula, how it works ?

 

[Peter_SSs]

can you just elaborate the formula, how it works

Take row 4

=IFERROR(INDEX(A4:F4,AGGREGATE(14,6,(COLUMN(A4:F4)-COLUMN(A4)+1)/(A4:F4<>""),2)),"N/A")
Blue part makes an array of column numbers {1,2,3,4,5,6}
Red part makes an array of True/False values {T,T,F,T,F,T}

=IFERROR(INDEX(A4:F4,AGGREGATE(14,6,({1,2,3,4,5,6})/({T,T,F,T,F,T}),2)),"N/A")

When doing the arithmetic of blue divided by red, T = 1 and F = 0 so the results array is
{1,2,#DIV/0!,4,#DIV/0!,6}

AGGREGATE(14,6,.....,2) says to take the second highest value ignoring errors = 4

INDEX then retrieves the value from the 4th cell in A4:F4. This is 9 from D4.

 

[Kulsumbi]

Take row 4

=IFERROR(INDEX(A4:F4,AGGREGATE(14,6,(COLUMN(A4:F4)-COLUMN(A4)+1)/(A4:F4<>""),2)),"N/A")
Blue part makes an array of column numbers {1,2,3,4,5,6}
Red part makes an array of True/False values {T,T,F,T,F,T}

=IFERROR(INDEX(A4:F4,AGGREGATE(14,6,({1,2,3,4,5,6})/({T,T,F,T,F,T}),2)),"N/A")

When doing the arithmetic of blue divided by red, T = 1 and F = 0 so the results array is
{1,2,#DIV/0!,4,#DIV/0!,6}

AGGREGATE(14,6,.....,2) says to take the second highest value ignoring errors = 4

INDEX then retrieves the value from the 4th cell in A4:F4. This is 9 from D4.

Thanks

 

[Peter_SSs]

You're welcome.