Excel - Return Value Based on Key Words Within Cell

[jessgirl]

Hello! I am new to these forums, but have been using the resources for awhile now. I am stuck on how to approach this issue explained below, even after searching for similar problems.


I am looking for a formula that can search one column for specific key words, and then return a secondary value based on the key words found.


For example, below I have a list of phones/tablets. The product names for Apple, for example, are all different, so I am trying to just find a formula that would return "Apple" as the manufacturer.


I saw a few different approaches in the archives, like using the wildcard function, however the trouble comes when I have examples like with Motorola. The manufacturer's name isn't necessarily the first part of the product name.


-------------------

Product Name

-------------------

Apple iPhone 4S

Apple iPhone 5

Motorola - Droid X

Droid X by Motorola

Motorola-Droid E600

Samsung Galaxy 3

Prepaid Samsung Gusto

--------------------

Manufacturer List

--------------------

Apple

Motorola

Samsung

--------------------

I would like to the Product Names to return the correct Manufacturer, as seen below:


--------------------------------------

Product Name ------------Manufacturer

--------------------------------------

Apple iPhone 4S -----------Apple

Apple iPhone 5 ------------Apple

Motorola - Droid X ---------Motorola

Droid X by Motorola --------Motorola

Motorola-Droid E600 --------Motorola

Samsung Galaxy 3 ----------Samsung

Prepaid Samsung Gusto -----Samsung


Is this possible? Thanks for your time.

 

[SirJB7]

Hi, jessgirl!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself -I yet read you've already passed thru there-, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


Assuming that 1st list is in column A and 2nd list in column D, in column B try this array formula:

B2: =INDICE(D$2:D$4;COINCIDIR(VERDADERO;NO(ESERR(ENCONTRAR(D$2:D$4;A2)));0)) -----> in english: =INDEX(D$2:D$4,MATCH(TRUE,NOT(ISERR(FIND(D$2:D$4,A2))),0))


Remember that array formulas should be entered with Ctrl-Shift-Enter instead of just Enter.


Note: also pray that no company (Samsung for example) releases a model named with other company name within it (like Samsung X45-HTCLOUD for example) or you'd be in trouble.


Regards!

 

[jeffreyweir]

Nice formula, SirJB7. I tried using AGGREGATE(9,6,FIND(Maker,K3)) instead of MATCH(TRUE,NOT(ISERR(FIND(D$2:D$4,A2))),0) and was really surprised to find out that it didn't work. It seems AGGREGATE only accepts arrays from the spreadsheet. That is poor programming by MS, in my opinion.

 

[SirJB7]

@jeffreyweir

Hi!

Thanks, I didn't think about using AGGREGATE function as I don't use it frequently, it seems as that of mine arises automatically.

Regards!

 

[Haseeb A]

Another way;


=LOOKUP(9.9E+300,SEARCH(Maker,A1),Maker)


Jeff;


For AGGREGATE, divide ROW(Maker)/(ISNUMBER(FIND(Maker,A1)), so will give #DIV error for FALSE & ROW # for TRUE values, then use LARGE or SMALL in function_num.


=INDEX(G:G,AGGREGATE(15,6,ROW(Maker)/(FIND(Maker,A1)>0),1))


Assuming Maker is in column G, using G:G can avoid ROW(Maker)-MIN(ROW(Maker))+1


Hsseeb

 

[jeffreyweir]

Thanks Haseeb. I forgot that rows bit.

 

[jessgirl]

Thank you everyone who replied! I am going to try a few of these out.


Unfortunately there are a few exceptions where parts of names overlap, but not many.