Fill columns with value based on content of another cell

[MisterMints]

Hi all,

Looking for a simple way to fill a cell based on the content of another cell. Wasn't sure what really to search for, so apologies if this is a question that's been asked to death.

I have a spreadsheet with almost 1000 populated rows. In column C, I need to report whether the content in the same row of column E is an internal or external website. Not all the URLs belong to the same domain (e.g. some are mycompany.com, others are mysimililarnamecompany.com).

Almost world example, I might want to report that any string containing the word "Google" is Internal and that would cover google.com, google.co.uk, googleanlytics.com, googlemaps.com, but anything else, such as bbc.co.uk, bing.com is flagged as External.

Does that make sense? Is there an easy way to do this while searching against multiple string values?

Thanks

 

[Larry Haydn]

I am not smart enough to find a solution using just 1 cell's formula,
but if you use 2 formulas in separate cells, you can get the result.

Lets say column J is blank - then enter a formula here:
=IFNA(VLOOKUP("*google*",$E2:$E2,1,FALSE),"Ext")
This will return a result that is either "Ext", or the full web address.

With this result, you can then write this formula in column C:
=IF($J2:$J2="Ext","External","Internal")
This will give you the desired result.