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)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Ladies finger[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Jasmin[/TD]
[TD]Flower[/TD]
[/TR]
</tbody>[/TABLE]



Table: 2 (My question)

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Apple | Orange | Banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple | Jasmine[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Carrot [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apple [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ladies finger; Orange[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table: 3 (outcome)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Apple | Orange | Banana[/TD]
[TD]Fruits & Fruits & Fruits[/TD]
[/TR]
[TR]
[TD]Apple | Jasmine [/TD]
[TD]Fruits & Flower[/TD]
[/TR]
[TR]
[TD]Carrot [/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]Apple [/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]Ladies finger; Orange[/TD]
[TD]Vegetable & Fruit[/TD]
[/TR]
</tbody>[/TABLE]

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".

[TABLE="width: 554"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD]Apple | Orange | Banana[/TD]
[TD]Fruit & Fruit & Fruit[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD]Apple | Jasmine[/TD]
[TD]Fruit & Flower[/TD]
[/TR]
[TR]
[TD]Ladies finger[/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[TD][/TD]
[TD]Apple | finger[/TD]
[TD]Fruit & Flower[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Fruit[/TD]
[TD][/TD]
[TD]Ladies finger; Orange[/TD]
[TD]Fruit & Vegetable & Gender[/TD]
[/TR]
[TR]
[TD]Jasmin[/TD]
[TD]Flower[/TD]
[TD][/TD]
[TD]lettuce | spinach | apple[/TD]
[TD]Fruit & vegetable & vegetable[/TD]
[/TR]
[TR]
[TD]lettuce[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD]Brussels | zucchini[/TD]
[TD]vegetable & vegetable[/TD]
[/TR]
[TR]
[TD]spinach[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD]celery[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]Brussels[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD]onion | fofu | soybeans[/TD]
[TD]vegetable & Legumes[/TD]
[/TR]
[TR]
[TD]zucchini[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD]lentil flour | broccoli[/TD]
[TD]Legumes & vegetable[/TD]
[/TR]
[TR]
[TD]celery[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD]Ladies finger[/TD]
[TD]Vegetable & Gender[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]soybeans[/TD]
[TD]Legumes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lentil flour[/TD]
[TD]Legumes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]broccoli[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]vegetable[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ladies[/TD]
[TD]Gender[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.