Fill columns with value based on content of another cell

MisterMints

New Member
Joined
Jun 29, 2015
Messages
7
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
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
92
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,099
Messages
5,576,127
Members
412,698
Latest member
Lilly13m
Top