Iif portion of string exists in one cell, return corresponding column?

[CalianasMamma]

I've been trying to figure this out but I need help!

I want to associate the type of school, based on their email address. My first sheet has a list of email address.

I have another sheet with a list of domains and what type of school it is. Is there a way that I can do some kind of a wildcard search to see if the email address (sheet1, columnA) contains the domain (sheet2, col A), and, if so, return the 'SchoolType' as a result?

Thanks to anyone who can help me! I've been trying but the wildcard part of it really throws me off. I'm looking for case insensitive.

 

[Jonmo1]

Welcome to the board.

IN the sheet with just the email addresses.
B2: =LOOKUP(2^15,SEARCH('OtherSheet'!A$2:A$6,A2),'OtherSheet'!B$2:B$6)

Note, there cannot be any blanks in OtherSheet!A2:A6

 

[djrod1rep]

Enter this formula in Cell B2:

=IFERROR(VLOOKUP(TRIM(RIGHT(A2,LEN(A2)-FIND("@",A2,1))),Sheet2!A:B,2,FALSE),"DOMAIN Not Found.")

Copy down to the necessary rows in Column B (Sheet1).
This will lookup the SchoolType from Sheet2. It will also give you a DATA VALIDATION message if the Domain is not found. So you will be able to identify any new or not listed domain names in your data.

 

[Jonmo1]

After looking closer, that may be overthinking it..

Try

=VLOOKUP(REPLACE(A2,1,FIND("@",A2),""),'OtherSheet'!$A$2:$B$6,2,0)

 

[CalianasMamma]

Awesome, thank you, that worked like a charm!!

In a separate formula, I'd also like to look for an email fragment, that has the same kind of wildcard. To see if "DomainFragment" (colA), appears in the "Email" (colA), and if so, return the corresponding value "Type" from Col B, like this:

I'm trying to re-purpose formula you gave me above, but I must be doing something wrong. Help!

 

[Jonmo1]

For that one, try the first formula I posted in Post #2

 

[CalianasMamma]

Ahhh, that works really well, thank you! For some reason, though it seems to be tripping up on schools. and publicschools, from the first table in post #5. For those records, it's returning "#N/A" Is there a character limit on it, perhaps?

 

[Jonmo1]

Works fine for me.

Can you try using an HTML tool like ExcelJeanie (see my signature for a link)
To post a nicely formatted table to your post.
Pictures don't really help much, because we can't copy paste data from them to our own sheet for testing.
And most people won't take the time to retype your tables by hand.

 

[CalianasMamma]

Good to know; I'll do that next time.

On my question and your reply in Post #8, I realized that I wasn't including all my rows correctly in the formula - once I fixed that, it works fine. Thank you so much!!

 

[Jonmo1]

You're welcome