Vlookup with strings

[jgopalk]

Hi there,

I tried lot to fix this. But I couldn't make it up. Kindly guide me in this.

I have keywords with few strings. I need to get those words too by vlookup.

For example:
Table: 1 (Reference sheet)

Apple	Fruit
Orange	Fruit
Ladies finger	Vegetable
Carrot	Vegetable
Banana	Fruit
Jasmin	Flower

<tbody>
</tbody>

Table: 2 (My question)

Apple | Orange | Banana
Apple | Jasmine
Carrot
Apple
Ladies finger; Orange

<tbody>
</tbody>

Table: 3 (outcome)

Apple | Orange | Banana	Fruits & Fruits & Fruits
Apple | Jasmine	Fruits & Flower
Carrot	Vegetable
Apple	Fruit
Ladies finger; Orange	Vegetable & Fruit

<tbody>
</tbody>

Here, already I have Table 1 with ~ 20000 keywords. I have “Table 2” to be filled like “Table 3”. I will have maximum of two strings. One is “ | “ and next is “ ; ”.

For single keyword, I know we can directly apply Vlookup. But this case of stings how can we proceed further?

Hope I have explained my requirement clearly. Kindly let me know if you have any further questions.

My extreme thanks in advance.

Gopal

 

[Aladin Akyurek]

Hi Aladin,

See in Ladies finger the formula consider that as two words and giving two results (highlighted in red). Single cell content need to be considered as single value. For Ladies finger result should be Vegetable alone and not "Vegetable + Gender".

Apple	Fruit		Apple | Orange | Banana	Fruit & Fruit & Fruit
Orange	Fruit		Apple | Jasmine	Fruit & Flower
Ladies finger	Vegetable		Carrot	Vegetable
Carrot	Vegetable		Apple | finger	Fruit & Flower
Banana	Fruit		Ladies finger; Orange	Fruit & Vegetable & Gender
Jasmin	Flower		lettuce | spinach | apple	Fruit & vegetable & vegetable
lettuce	vegetable		Brussels | zucchini	vegetable & vegetable
spinach	vegetable		celery	vegetable
Brussels	vegetable		onion | fofu | soybeans	vegetable & Legumes
zucchini	vegetable		lentil flour | broccoli	Legumes & vegetable
celery	vegetable		Ladies finger	Vegetable & Gender
onion	vegetable
soybeans	Legumes
lentil flour	Legumes
broccoli	vegetable
potato	vegetable
Ladies	Gender

<tbody>
</tbody>

Kindly suggest me how to move forward.

Thanks again for your help in this.

Gopal

1. Sort A:B on column A in ascending order.

2. In D (input sentences) replace ; with |.

3. Now invoke: Control+sshift+enter...

=REPLACE(aconcat(IF(ISNUMBER(SEARCH(SUBSTITUTE($A$2:$A$18," ","")&"|",SUBSTITUTE($D2," ","")&"|"))," & "&$B$2:$B$18,"")),1,3,"")

and copy down.