Evaluate 5 columns and return right-most name
 

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!

Evaluate 5 columns and return right-most name
 

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


"JulesMacD" wrote in message
...
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!

Evaluate 5 columns and return right-most name
 

Or a bit shorter

=LOOKUP("α",A1:E1)

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


"T. Valko" wrote:

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


"JulesMacD" wrote in message
...
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!

Evaluate 5 columns and return right-most name
 

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

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



Lori wrote:

Or a bit shorter

=LOOKUP("α",A1:E1)

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

"T. Valko" wrote:

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


"JulesMacD" wrote in message
...
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!




--

Dave Peterson

Evaluate 5 columns and return right-most name
 

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.

"T. Valko" wrote:

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


"JulesMacD" wrote in message
...
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!

Evaluate 5 columns and return right-most name
 

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.

"JulesMacD" wrote in message
...
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.

"T. Valko" wrote:

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


"JulesMacD" wrote in message
...
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!

Evaluate 5 columns and return right-most name
 

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.

"Dave Peterson" wrote:

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

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



Lori wrote:

Or a bit shorter

=LOOKUP("α",A1:E1)

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

"T. Valko" wrote:

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


"JulesMacD" wrote in message
...
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!




--

Dave Peterson

Evaluate 5 columns and return right-most name
 

=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!


--
Biff
Microsoft Excel MVP


"Fred Smith" wrote in message
...
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.

"JulesMacD" wrote in message
...
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.

"T. Valko" wrote:

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


"JulesMacD" wrote in message
...
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!