How to search for specific words in text string and assign category based on these words

May be this

Formula:

Please Login or Register  to view this content.

You could use find instead of search to mach case where you can eliminate error as you mentioned

=LOOKUP(1E+100,SEARCH(D$2:D$6,A2),F$2:F$6)
Try this

Find text.xlsxThis also works with your example

Formula:

Please Login or Register  to view this content.

For the second part of your question. It is a case of "First come first served". This goes both for the order of the items in column D and the order of the text in each cell of column A.

Try

Edit Cell D6as "Soda" and F6 as "Beverage"

[QUOTE=
For example, there are some cells like "InL CkBx, Soda 20oz" and"Soda Can".
If I would like the first example to be categorized as "Indian" and second as "Beverage" what do I do?
[/QUOTE]

Hi,

an example:

InL CkBx (Regular Price), Soda Can (Regular Price)


contains three (edit) possible "match" to E2:E31: "InL", "Soda Can" and "Soda".

using INDEX(...(MATCH(TRUE... you'll get the first one

using VLOOKUP(LOOKUP(9999... the last one

If you play with the order list , the "hierarchy" of E2:E31, as formulas read the list from E2 to E35, you could get a better alignment between two formulas outputs.

Forgive my poor early morning English.

Regards

Hi again,

an example: you could write in E32 "InL" and G32 "Indian", after having set new ranges (E2:E32) read by formulas you will record new outputs from VLOOKUP(LOOKUP.

Hope it's a little help.

Regards

Ciao,

thanks for your kind feedback.




A little experiment to study

=VLOOKUP(LOOKUP(E1+100,FIND(E$2:E$32,A2),E$2:D$32),$E$2:$G$32,3,0)


go the formula bar, select only the segment

FIND(D$2:D$6,A2)

and press F9

you'll see a series of #VALUE! errors that LOOKUP ignores. LOOKUP is searching for a big number -E1+100 (you could substitute with LEN(A2)+1) and stops its hopeless search at the last number/position produced by FIND (...).

More or less.

Saluti da Firenze

Ciao,

you're welcome.

In the attached example there is a new list (old list but different hierarchy).

The string "Btld Wtr (Regular), InL CkBx (Regular Price)" contains 3 substrings of the list: Wtr, In and InL.

Using F9 over LOOKUP you will see:

LOOKUP(1E+100,{6.#VALUE!.#VALUE!.#VALUE!.21.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.#VALUE!.21.#VALUE!}

"Wtr" has been spot after 5 characters (Btld"space") in position 6 , In and InL after 20 characters in position 21 ("Btld Wtr (Regular), ).

In the example Inl is the second 21 produced by LOOKUP, the last number: so LOOKUP(E1+100... returns the position of last number occurred: in the example InL/Indian.

I hope not to be wrong.

In lookup formulae, the order of lookup means everything. The order of the lookup list is counter-intuitive in that if you have values like IN and INK you will want to have INK before IN because the formula (normally) will find IN and stop if it finds that and INK will not be found if it is after IN.

INDEX lists the values that you are wanting to retrieve followed by a row reference and a column reference. The row and column references can be determined by a variety of means and the order of the columns isn't important. This is followed by the type of return you want...exact or not

VLOOKUP requires the value to be looked up then the ARRAY and the column followed by the type of return...exact or not.

Regardless of the method that you prefer and your data permits, the order of the list holding the compare items can greatly simplify the formula required to retrieve the correct value. This is likely the only part of the data that you have control over as the order of the values to be found in "free" text can be any order at all.

The article at this location http://www.randomwok.com/excel/how-to-use-index-match/ gives pretty good explanations and comparisons of VLOOKUP and INDEX MATCH for looking up data.