Thread: Vlookup cell contents?

Hi fellow excel users,

I have a problem I'm trying to solve and I'm hoping that some excel genius can help me with.

I have a database of 13,000 rows of contact names with about 5 columns of information each on things like email address, company etc. Each month I need to wade through a new list of > 10,000 and tag them with industry information which is highly tedious.

For example, for a contact to be labelled 'Academic' one criteria could be that their email address contains '.edu' amongst others.

Is there a way for me to make use of VLOOKUP but instead of a 100% cell match, the cell just needs to 'contain' the '.edu' and returns the label Academic? And does the same for my entire list of criteria and their corresponding labels?

I managed to create a formula with IF(SEARCH(CELL"contents")))that will label any row that contains '.edu' as Academic. However this is very tedious as I have about 30 criteria just for Academic alone which involves me having 30 columns of this function just for Academic names.

Another method I employed in the past was autofilter > custom > if cell contains '.edu' then manually label Academic. And repeat process for the rest of the 30 criteria and labels.

I would appreciate any help greatly.