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

D

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.
 
B

Bob Umlas

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

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
 
J

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)))
 
D

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!
 
D

Dave The Favorite

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

Dave The Favorite

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top