0

I have five columns of data (A-E), most of which are blank. In column F, I want to display one value from the 5 cells in that row. Specifically, I want to display the rightmost cell with a value. For example, if A3 and D3 both have values, then it should populate with D3. If only B4 has a value, then I'd like that value. They all have formulas in them, but most of the formulas result in blanks.

I suspect this could be accomplished with nested IFs, but I'm hoping for a more concise way.

Improve this question

1 Answer 1

Reset to default
3

Use INDEX(MATCH())

=IFERROR(INDEX(A3:E3,MAX(IFERROR(MATCH(1E+99,A3:E3),0),IFERROR(MATCH("zzz",A3:E3),0))),"")

enter image description here

Since it is only 5 columns and if you have Office 365 Excel then you can use IFS():

=IFERROR(IFS(E3<>"",E3,D3<>"",D3,C3<>"",C3,B3<>"",B3,A3<>"",A3),"")

enter image description here

The first is more scale-able if other columns are added while the second is a little shorter with only 5 columns.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Can you explain what the "1E+99" and "ZZZ" portions are doing? Thanks!
The two matchs find the last cell that have numbers OR the last cell with text. The 1E+99 is an impossibly large number so and "ZZZ" is most likely the last alphabetically word one could find. So since match does not find a match it returns the last cell where that value is less. If your cells are always one or the other we can get rid of the MIN and the unneeded match. @gattoun
@gattoun remember that if this worked for you, to mark as correct by clicking the check mark by the answer.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.