Excel Formula Similar to Select-Case

Question

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.

Answer 1

Use INDEX(MATCH())

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

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),"")

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