| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| vlookup to return 2 columns | Excel Discussion (Misc queries) | |||
| Can Excel evaluate one cell and return the information in another? | Excel Discussion (Misc queries) | |||
| Function evaluate multiple cells and return 1st one w/a value | Excel Discussion (Misc queries) | |||
| how can you auto return from columns B1 to A2 | Excel Discussion (Misc queries) | |||
| want sumif function's range to evaluate 2 columns | Excel Worksheet Functions | |||
Linear Mode
