I believe what you want (in this case for row 1) is:
INDEX(C2:E2,COUNTIF(C2:E2,"*"))
Here's what it does:
COUNTIF
gives you the number of cells that satisfy a condition. The condition I used was"*"
which means any character. So if only two of the cells inC2
toE2
have a value, it will return 2, but if all three are present it will return 3.INDEX
will return the value of a cell in a range of cells. Our range here is againC2
toE2
and we've said in that range, give me the Xst cell, where X is the number thatCOUNTIF
returns.
All together it's something like "count the number of cells that have something in them, use that number to get me last cell."
You'll have to change the range for each row, but numbers might be able to figure it out if you do a couple.