Hello All, I'm a forum virgin and have been going crazy trying to solve a problem. I hope this wont be but I believe this is a tough one!

I have a large database of email addresses. I want to return the country, company name and first name and remove emails containing certain "bad" words. e.g. "unsubscribe".

Purpose to upload to my database. Most of this can be obtained using the SEARCH function. i.e. if the cell contains ".co.uk" then return "United Kingdom". I started manually adding them to formula but with nearly 200 variables this has already become unmanageable.

e.g. =IF(ISNUMBER(SEARCH("*.za*",C2)),"South Africa",IF(ISNUMBER(SEARCH("*.mu*",C2)),"Mauritius",IF(ISNUMBER(SEARCH("*.ke*",C2)),"Kenya" etc etc etc.

I can apply this to the other fields. For first name though there are potentially 1000's of variables!

So my thought is, can I combine the SEARCH and VLOOKUP functionality, so I just make a list of variables rather that putting them into a formula.

For "First Name" I started this one =IF(ISNUMBER(SEARCH($G$2,B2)),$G$2,IF(ISNUMBER(SEARCH($G$3,B2)),$G$3,IF(ISNUMBER(SEARCH($G$4,B2)),

So it does look at a list but for me to lengthen this formula to hundreds is too fiddly and time consuming.

Or is there a much easier way?!

This would save hours of manual work if I can crack it :-)

Fingers crossed!