Formula for finding specific elements/ member in a list

[shade]

Dear Excel Community,

I have a listing of Key Companies. What I would like is a formula that I can paste next to a column of company names in which a company which matches any of the companies on my Key company list is identified.

For example my key company list is "Apple, Microsoft, Best Buy, McDonalds, and Firestone", and the company listed in my comparison list is "Microsoft". I would like the formula to say "Key Company". If the company is not on my list, the output would be blank (i.e., " ").

I have been trying to use the Find and Search functions. Please note that my true key company list has 45 companies. What I have been trying to avoid is writing a huge statement with several "If Statements". Also, would like to use a function instead of writing VBA program.

Any help would be much appreciated.

Regards,

Shade

 

[prabby25101981]

Try this -

=IF(ISERROR(VLOOKUP(A2,Key_Companies!A2:A100,1,0)),"","Key Company")

Key_companies is the name of the sheet where your list of key companies resides. Change this accordingly. In case you have any issues, try looking up VLOOKUP in excel help.

 

[Aladin Akyurek]

Try...

=IF(ISNUMBER(MATCH(A2,KeyList,0)),"Key Company","")

KeyList refers to a range housing the key companies of interest.

 

[pagrender]

Hi, and welcome to the Board.

If your Key Company list is in the range A1:A5, and your second column in Column B, you can put this formula in C1:
=IF(ISERROR(VLOOKUP(B1,$A$1:$A$5,1,FALSE))," ","Key Company")

Let us know if that works for you.

Pete

 

[AlphaFrog]

Thy something like this...

=IF(COUNTIF(D:D, "*"&A1&"*")>0, "Key Company","")

If the company in cell A1 is found in any cell in column D, then it will be tagged as a Key Company.

 

[shade]

AlphaFrog,

Thanks for the formula. A question for you, how would I modify this code so that the company is compared to a Key word listing? The problem I am having is that for example, "Apple" is the Company/Key word I am looking for, but the company name I am comparing to it might have listed the company as "Apple, Inc.".

In this case I would consider it a key company because it has the Key word "Apple" in the name, but the code won't identify it because it was not written exactly the same.

Is it possible to build a Key Word Company listing so that if any of the Key words is found in the company name, it will be identified as a "Key Customer". So if "Microsoft" is the key word, "Microsoft, Inc.", "Microsoft Incorporated", or "Microsoft, Inc. Ltd US" would still be identified as a Key Company because it contains the Key word company name. I know that the "*" wildcard character is used, I just don't know where.

Thanks,

Shade

 

[prabby25101981]

I think Alphafrog's formula will work on that also...

 

[AlphaFrog]

=IF(COUNTIF(D:D,TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",50)),50))&"*")>0,"Key Company", "")

This checks if the 1st word in A1 is found as the 1st word anywhere in column D. Does that work for you?

This isn't perfect. I don't know how diverse your data is. It may work. It wouldn't work if you had two companies with the same first word and only one was a Key company e.g.

John Hancock Life Insurance Company
John Smith Shipping, Inc.