Evaluate 5 columns and return right-most name

[JulesMacD]

I have 5 columns containing different supervisor levels. Some columns are
blank. I would like to write a formula to evaluate the 5 columns and return
the name in the farthest right column. Thank you!

 

[T. Valko]

Try one of these:

=INDEX(A1:E1,MATCH(REPT("Z",255),A1:E1))

=IF(COUNTIF(A1:E1,"*"),INDEX(A1:E1,MATCH(REPT("Z",255),A1:E1)),"")

 

[Lori]

Or a bit shorter

=LOOKUP("α",A1:E1)

where "α" is the alpha character [Alt+224] or inserted with insert>symbol.

 

[Dave Peterson]

Just because I could never remember how I made that funny character:

=LOOKUP(char(224),A1:E1)

Or a bit shorter

=LOOKUP("α",A1:E1)

where "α" is the alpha character [Alt+224] or inserted with insert>symbol.

Try one of these:

=INDEX(A1:E1,MATCH(REPT("Z",255),A1:E1))

=IF(COUNTIF(A1:E1,"*"),INDEX(A1:E1,MATCH(REPT("Z",255),A1:E1)),"")

 

[JulesMacD]

I've tried all 4 formulas and none are working. Perhaps I should show an
example.

A B C D E
Smith,T Jones,B
Smith,T Jones,B Green,J
Jones,B Gray,J Brown,Q Black,E

I tried =if(E2="",D2,if(D2="",C2,IF(C2="",B2,IF(B2="",A2,"check")))) but
it's returning a '0'. The farther you go to the right, the more direct the
supv so I want the name in the farthest right column. I hope this makes
sense.

 

[Fred Smith]

If you want help, you need to say something more than 'not working'. What
didn't work? What results did you get.

Regarding your If formula, you need to check for non-blanks, rather than
blanks. Something like:
=if(E2<>"",E2,if(D2<>"",D2,IF(C2<>"",C2,IF(B2<>"",B2,A2))))

Regards
Fred.

 

[Lori]

Actually char(224) is equivalent to [Alt+0224] which is "à" and this will not
work as the lookup value needs to always sort to the bottom of the list.

Without a leading zero, [Alt+224] is ascii, see: http://www.asciitable.com/.
In windows unicode it is 03B1 in hex or chrw(945) in vba and any other such
extended character should also work.

Just because I could never remember how I made that funny character:

=LOOKUP(char(224),A1:E1)

Or a bit shorter

=LOOKUP("α",A1:E1)

where "α" is the alpha character [Alt+224] or inserted with insert>symbol.

Try one of these:

=INDEX(A1:E1,MATCH(REPT("Z",255),A1:E1))

=IF(COUNTIF(A1:E1,"*"),INDEX(A1:E1,MATCH(REPT("Z",255),A1:E1)),"")


--
Biff
Microsoft Excel MVP


I have 5 columns containing different supervisor levels. Some columns are
blank. I would like to write a formula to evaluate the 5 columns and
return
the name in the farthest right column. Thank you!

 

[T. Valko]

=if(E2<>"",E2,if(D2<>"",D2,IF(C2<>"",C2,IF(B2<>"",B2,A2))))

I would include a test for A2 as well.

I've tried all 4 formulas and none are working.
I tried =if(E2="",D2,if(D2="",C2,IF(C2="",B2,IF(B2="",A2,"check"))))
but it's returning a '0'.

Hmmm...

All of the suggested formulas *should* work. If the cells contain formulas
that return formula blanks ("") and that is the rightmost cell then that
will be the result of the formula. However, if your IF formula is returning
0 then there must not be formula blanks in the cells.

So, I don't know why none of the suggested formulas don't work!