Next-To-Last, Non-Blank, Rightmost Value in a Row

[Dave The Favorite]

Consider a single row and ten columns.

Some or all or none of the cells in the row will have non-numerical data.

Can anyone provide a formula that will display the next-to-last, non-blank,
rightmost value in the row?

From the sample data below, the result would be "Dog"
Column A - Mom
Column B - Dad
Column C - (blank)
Column D - Sis
Column E -(blank)
Column F - Bro
Column G - Dog
Column H - (blank)
Column I - (blank)
Column J - Cat
Column K - (FORMULA WHICH PROVIDES THE ANSWER)

Thank you for your consideration and support.

 

[Bob Umlas]

Ctrl+Shift+enter:
=INDEX(1:1,LARGE((A1:J1<>"")*COLUMN(A:J),2))
Bob Umlas
Excel MVP

 

[Mike H]

Hi.

Try this ARRAY formula

=INDEX(A1:J1,LARGE((A1:J1>"")*COLUMN(A1:J1),COUNTIF(A1:J1,">""")+1-(COUNTA(A1:J1)-1)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[JBeaucaire]

With those values in row 1, try this, then copy down:

=INDEX(A1:J1, LOOKUP(99^99, 1/((A1:J1<>"") * (COLUMN(A1:J1) < LOOKUP(99^99,
1/(A1:J1<>"") * (COLUMN(A1:J1))))) * COLUMN(A1:J1)))

 

[Dave The Favorite]

Mike, it works perfectly. Thanks to for the reminder about entering array
formulas. You're a good man....and there are so darned few of us left.
Thank you!

 

[Dave The Favorite]

This one worked perfectly too. Sir/Ma'am, you are a credit to your family,
friends and country. Thank you!

 

[Dave The Favorite]

Perfect! Thank Mr. Umlas. Put yourself in for a raise and I'll approve it!