Excel - if cell contains..

[77winston]

Hi, Hope someone can assist with this one.
I have a spreadsheet with column A containing series of email addresses, in the column B I'd like to assign a value based on email domain in Column A.
I tried the below forumla and it works, but stuck on how to extend it so it will cater for all domain variations.
=IF(ISNUMBER(SEARCH("gmail",A3)),"Segment2","")
Values to assign for domains are

Assignments
Domains	Segment
hotmail	segment 1
gmail	segment 2
yop	segment 3
yahoo	segment 4

Sample of expected result

Email_Address	Segments
test.test@gmail.com	Segment2
test@yop.com
tester@yahoo.com
testing@hotmail.com

Hope it makes sense.

 

[Peter_SSs]

Could you construct a lookup table and then use a formula like this?

23 03 10.xlsm
	A	B	C	D	E
1	Email_Address	Segments		Domains	Segment
2	test.test@gmail.com	segment 2		hotmail	segment 1
3	test@yop.com	segment 3		gmail	segment 2
4	tester@yahoo.com	segment 4		yop	segment 3
5	testing@hotmail.com	segment 1		yahoo	segment 4
Domain

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(REPLACE(LEFT(A2,FIND(".",A2,FIND("@",A2))-1),1,FIND("@",A2),""),D$2:E$5,2,0)

 

[77winston]

Could you construct a lookup table and then use a formula like this?

23 03 10.xlsm
	A	B	C	D	E
1	Email_Address	Segments		Domains	Segment
2	test.test@gmail.com	segment 2		hotmail	segment 1
3	test@yop.com	segment 3		gmail	segment 2
4	tester@yahoo.com	segment 4		yop	segment 3
5	testing@hotmail.com	segment 1		yahoo	segment 4
Domain

Cell Formulas
Range	Formula
B2:B5	B2	=VLOOKUP(REPLACE(LEFT(A2,FIND(".",A2,FIND("@",A2))-1),1,FIND("@",A2),""),D$2:E$5,2,0)

Awesome! Did not think of this approach. Will try it out.
Thank you.

 

[Peter_SSs]

Cheers. Let us know (with examples) if it does not meet your need.