Help with formula

[tommcbrny]

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 "@companyxyz.com".

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(C2,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

 

[Bernie Deitrick]

Tom,

=IF(B2="",IF(NOT(ISERROR(FIND("@",C2))),VLOOKUP(MID(C2,FIND("@",C2)+1,LEN(C2)),E:F,2,FALSE),"Internal"),B2)

HTH,
Bernie
MS Excel MVP