Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula
Hello,
I have a worksheet with two columns (B, C) of information that will drive data in a 3rd column (D) via either copying the value from column B or, if B is empty, evaluating data in column C and then printing "Internal" or performing a lookup based on the information in C. C contains usernames. If the username in C is internal, there is no domain following the username. If the username is external, the username is followed by ". If B is blank, I want to next check C for an @ symbol. If there is no @ symbol, I want D to equal "Internal". If B is blank and C contains an @ symbol, I want to perform a lookup to match the domain with another text value to place in D. I have the second part working, where the forumula checks B and if blank then looks up and prints a text value to D based on the information following the @ symbol. I cannot get the first part working, however, where the formula first checks for the @ symbol and if not found, prints "Internal" to D. Here is the formula I tried: =IF(MID(B2,1,3)="TID",B2,IF(FIND("@",C2),VLOOKUP(C 2,E:F,2,FALSE),"Internal")) Cells in C with no @ symbol return #VALUE!. Cells where B has a proper ID value or where there is an @ symbol return the proper ID (either from B or from the lookup). Is what I'm trying possible? Thank you, Tom |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with formula
Tom,
=IF(B2="",IF(NOT(ISERROR(FIND("@",C2))),VLOOKUP(MI D(C2,FIND("@",C2)+1,LEN(C2)),E:F,2,FALSE),"Interna l"),B2) HTH, Bernie MS Excel MVP "tommcbrny" wrote in message ... Hello, I have a worksheet with two columns (B, C) of information that will drive data in a 3rd column (D) via either copying the value from column B or, if B is empty, evaluating data in column C and then printing "Internal" or performing a lookup based on the information in C. C contains usernames. If the username in C is internal, there is no domain following the username. If the username is external, the username is followed by ". If B is blank, I want to next check C for an @ symbol. If there is no @ symbol, I want D to equal "Internal". If B is blank and C contains an @ symbol, I want to perform a lookup to match the domain with another text value to place in D. I have the second part working, where the forumula checks B and if blank then looks up and prints a text value to D based on the information following the @ symbol. I cannot get the first part working, however, where the formula first checks for the @ symbol and if not found, prints "Internal" to D. Here is the formula I tried: =IF(MID(B2,1,3)="TID",B2,IF(FIND("@",C2),VLOOKUP(C 2,E:F,2,FALSE),"Internal")) Cells in C with no @ symbol return #VALUE!. Cells where B has a proper ID value or where there is an @ symbol return the proper ID (either from B or from the lookup). Is what I'm trying possible? Thank you, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|