Lookup query

Hi guys,

I have a long list of company names on an excel spreadsheet, around 122,000 records. Instead of individually going through each record, i wondered if there is a way to automate my request with a formula. Column D has the name of the company, and Column F will display a 0 if the company name includes the word/keyword 'broadband'. Do you guys know a quick way of doing this? Many thanks!

Jake

Put this in F1:

=IF(ISNUMBER(SEARCH("broadband",A1)),0,"some other message")

You can replace "some other message" with "" if you want an empty cell - you didn't specify.

Copy down to the bottom of your list.

Hope this helps.

Pete

Thank you sooooo much!!!

Is there a way to search if there are multiple keywords, for example:

=IF(ISNUMBER(SEARCH("broadband", "network", "internet", "domain",D1)),0,"1")

Many Thanks

hi,
here ( http://wikisend.com/download/988248/broadband.xlsx ) is the solution I'm proposing.
AL

@AL1976,

you don't really need the asterisks around the word broadband.

Pete

thanks Pete :-)

Originally Posted by Pete_UK

@AL1976,

you don't really need the asterisks around the word broadband.

Pete

Is there a way to search if there are multiple keywords, for example:

=IF(ISNUMBER(SEARCH("broadband", "network", "internet", "domain",D1)),0,"1")

Many Thanks

if ALL keywords must be present, replace OR by AND

=IF(OR(ISNUMBER(SEARCH("broadband",D1)),ISNUMBER(SEARCH("network",D1)),ISNUMBER(SEARCH("internet",D1)),ISNUMBER(SEARCH("domain",D1)))=true,0,"1")