One way:

="Stage " & COUNTIF(A3:D3,"""") & " " & LOOKUP(INDEX(A3:D3,
COUNTIF(A3:D3,"""")),{"C","I","S"},{"Complete","I ncomplete","Scheduled"}
)

=TEXT(LEN(A3&B3&C3&D3),"""Stage ""0 ") & LOOKUP(INDEX(A3:D3,
LEN(A3&B3&C3&D3)), {"C","I","S"}, {"Complete","Incomplete","Scheduled"})

In article ,
rbrychckn
wrote:

I've been trying this for days, and feel like I'm missing something
trivial.

I am pulling Status values (Complete, Incomplete, Scheduled) from
various sheets for a given person's file onto a Master sheet, using
Index/Match formulas. If there is no Status given for a stage/person,
the value of these formulas will be "". There are four statuses per
person in columns BA through BD (where these formulas reside). I want
to be able to take the last status for each person (in BE). Here is
some sample data:

BA BB BC BD BE
Stage1 Stage2 Stage 3 Stage 4 Total
C C C I Stage 4 Incomplete
S Stage 1
Scheduled

Now, the only way I have been able to differentiate what has a value
and what is "" is to do a COUNTIF(BA2:BD2, ""). However, I can't use
this with an offset, because what I would need is 4. The value of the
offset, however, is 0. What I would need is essentially y=-x+4 (ie,
0-- offset 4, 1--3, 2--2, etc). Can anyone help me with this
formula?

Thanks,
rbrychckn