Forumla for Multiple Text Conditions to Return Certain Conditional Values?

**v.parikh19** · 03-01-2011, 04:57 PM

I have a database list over 10,000 contacts that is organized by First Name, Last Name, Email Address and Company.

The last fields I need to sort and fill in are the Company field. Is there a formula I can use that can search the whole email column for multiple phrases like "@yahoo", or "@aig.com", or "@investmentfirm.com"? And then have it return a value in the company field that would show Yahoo, AIG, Investment Firm (respectively)? and if it there is an email that I haven't added in the conditional formula like "@fkdsjflkds.com" it just returns a blank response in the company field? PLEASE HELP!!!!!!!!

**NBVC** · 03-01-2011, 05:09 PM

Try:

Please Login or Register  to view this content.

where C2 contains first email to review.

copied down

**v.parikh19** · 03-01-2011, 05:58 PM

When you say...

Originally Posted by NBVC

Please Login or Register  to view this content.

what is the "z" referring to? I'm trying to tweak it because just using that code doesn't return anything. What about the Lookup value of "9.999E+307?" Did you mean to include that?

**NBVC** · 03-01-2011, 06:08 PM

see attached to see the formula does work as you explained you wanted the results to appear.

This part: LOOKUP(REPT("z",255),CHOOSE({1,2},""

is an error check.. and simply returns a blank if a match is not met as determined by the next part of the formula:

LOOKUP(9.99999E+307,SEARCH({"@aig","@yahoo","@investmentfirm.com"},C2),{"AIG","Yahoo","Investment Firm"}))

the 9.9999E+307 is referred to as a Big Number (the biggest Excel can work with)... Lookup looks for the last value in a specified range that is smaller than or equal to the lookup value... so it is looking for the Big Number and obviously can't find it so it will return the last number in the range.

The range, in this case is the resulting array from SEARCH({"@aig","@yahoo","@investmentfirm.com"},C2)

each part within the { } brackets is searched for within cell C2. If each value is found, it returns a number specifying the positon within C2 that the match starts... the other searches will yield errors since they won't be found.... so then Lookup() finds that number that is returned and then returns the item in the result_vector corresponding to where that number was found in the Lookup() function.

Hope that helps.

**v.parikh19** · 03-01-2011, 06:32 PM

Thank you so much for your help! There are way too many different email extensions I'm realizing. The list is over 10,000 contacts, so that would take very long to go through and assign a value for each differing email extention.

Thanks anyways, It did work the formula you suggested so thank u!

**NBVC** · 03-02-2011, 08:56 AM

You can always create a table of values on the side somewhere with one column listing all the various @addresses.. and in the next column their respective companies you want returned, then you can update the previous formula to something like:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",LOOKUP(9.99999E+307,SEARCH($X$2:$X$100,C2),$Y$2:$Y$100})))

where X2:Y100 contains the table of values... you can expand the table as desired.

Or you can extract the part with between the @ and the . and use that in the company column as is...

=MID(C2,FIND("@",C2)+1,FIND(".",C2,FIND("@",C2))-FIND("@",C2)-1)