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

 

[jgopalk]

Thanks all and apologies for not responding on time. I was in limited net access area.

Its working well. But I need few more alteration in the formulas.

1) If the keyword is not in the list and it comes in column D, it should reflects as N/A in the formulas. For example, potato is not in the list (A column), So when it comes in column D, it should reflect N/A in column E.

2)

Originally Posted by jgopalk
[...]

@Aladin: Yours looks cool. But I am using 2013. So couldnt get answer. Could you please make it for 2013?

[...]



In E2 control+shift+enter, not just enter, and copy down:

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

For this formula to work, add the following <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-top-style: initial; border-right-style: initial; border-bottom-style: dotted; border-left-style: initial; border-top-color: initial; border-right-color: initial; border-bottom-color: rgb(0, 0, 0); border-left-color: initial; border-image: initial; cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> code to your workbook as a module, using Alt+F11...

When I am using above formula, it considered keywords as separate words. For example, ladies finger considered as "ladies" and "finger". But it should taken as "ladies finger". Could you check this too?

3) If the result comes in order it would be more helpful. For example:

[TABLE="class: cms_table_grid"]
<tbody>[TR]
[TD]Ladies finger; Orange[/TD]
[TD]Fruit & Vegetable[/TD]
[/TR]
</tbody>[/TABLE]

the above should be Vegetable & Fruit.

Hope am clear. If anything more, kindly let me know.

Thanks again for your extensive work in this.

Gopal

 

[Aladin Akyurek]

1. A word that the keyword list does not list won't be reported in the results. That's the logic of the formula: it can only see what it looks for.

2. Hope this covers the issue...

{=REPLACE(aconcat(IF(ISNUMBER(SEARCH(SUBSTITUTE($A$2:$A$7," ","_"),SUBSTITUTE($D2," ","_")))," & "&$B$2:$B$7,"")),1,3,"")}

 

[jgopalk]

Thanks for your reply Aladin.

Find the below table which I got by using your recent formula. Its works fine Aladin. But few pointer I want to mention is:

1) Below I haven't enter apple and fofu (highlighted in red) in the main list. So, it should be reflected as N/A in the formula. But it is not there.
2) I don't understand why there is multiple & symbols. Is it possible remove additional & symbols ?


[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]lettuce | spinach | apple[/TD]
[TD]vegetable & vegetable & & & & & & & & & & & & & & & & & [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Brussels | zucchini[/TD]
[TD]vegetable & vegetable & & & & & & & & & & & & & & & & & [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]celery[/TD]
[TD]vegetable & & & & & & & & & & & & & & & & & [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]onion | fofu | soybeans[/TD]
[TD]vegetable & Legumes & & & & & & & & & & & & & & & & & [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]lentil flour | broccoli[/TD]
[TD]vegetable & Legumes & & & & & & & & & & & & & & & & & [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Could you please check above two points?

Thanks

Gopal

 

[Aladin Akyurek]

What is the list in A:B, which you are using? The formula (the most recent) of post #12 won't produce a string of & as you show!

 

[jgopalk]

Sorry Aladin. I couldn't get your point A:B.

[TABLE="width: 157"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]lettuce[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]spinach[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]Brussels[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]zucchini[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]celery[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]soybeans[/TD]
[TD]Legumes[/TD]
[/TR]
[TR]
[TD]lentil flour[/TD]
[TD]Legumes[/TD]
[/TR]
[TR]
[TD]broccoli[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]vegetable[/TD]
[/TR]
</tbody>[/TABLE]


I hope you are asking about this master list.

Am I clear?

 

[Aladin Akyurek]

Sorry Aladin. I couldn't get your point A:B.

[TABLE="width: 157"]
<tbody>[TR]
[TD]lettuce[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]spinach[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]Brussels[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]zucchini[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]celery[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]onion[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]soybeans[/TD]
[TD]Legumes[/TD]
[/TR]
[TR]
[TD]lentil flour[/TD]
[TD]Legumes[/TD]
[/TR]
[TR]
[TD]broccoli[/TD]
[TD]vegetable[/TD]
[/TR]
[TR]
[TD]potato[/TD]
[TD]vegetable[/TD]
[/TR]
</tbody>[/TABLE]


I hope you are asking about this master list.

Am I clear?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td] Apple[/td][td] Fruit[/td][td][/td][td] Apple | Orange | Banana[/td][td]Fruit & Fruit & Fruit[/td][/tr]
[tr][td]

3​

[/td][td] Orange[/td][td] Fruit[/td][td][/td][td] Apple | Jasmine[/td][td]Fruit & Flower[/td][/tr]
[tr][td]

4​

[/td][td] Ladies finger[/td][td] Vegetable[/td][td][/td][td] Carrot[/td][td]Vegetable[/td][/tr]
[tr][td]

5​

[/td][td] Carrot[/td][td] Vegetable[/td][td][/td][td] Apple | finger[/td][td]Fruit[/td][/tr]
[tr][td]

6​

[/td][td] Banana[/td][td] Fruit[/td][td][/td][td] Ladies finger; Orange[/td][td]Fruit & Vegetable[/td][/tr]
[tr][td]

7​

[/td][td] Jasmin[/td][td] Flower[/td][td][/td][td="bgcolor:#BDD7EE"] lettuce | spinach | apple[/td][td]Fruit & vegetable & vegetable[/td][/tr]
[tr][td]

8​

[/td][td] lettuce[/td][td] vegetable[/td][td][/td][td="bgcolor:#BDD7EE"] Brussels | zucchini[/td][td]vegetable & vegetable[/td][/tr]
[tr][td]

9​

[/td][td] spinach[/td][td] vegetable[/td][td][/td][td="bgcolor:#BDD7EE"] celery[/td][td]vegetable[/td][/tr]
[tr][td]

10​

[/td][td] Brussels[/td][td] vegetable[/td][td][/td][td="bgcolor:#BDD7EE"] onion | fofu | soybeans[/td][td]vegetable & Legumes[/td][/tr]
[tr][td]

11​

[/td][td] zucchini[/td][td] vegetable[/td][td] [/td][td="bgcolor:#BDD7EE"] lentil flour | broccoli[/td][td]Legumes & vegetable[/td][/tr]
[tr][td]

12​

[/td][td] celery[/td][td] vegetable[/td][td] [/td][td][/td][td] [/td][/tr]
[tr][td]

13​

[/td][td] onion[/td][td] vegetable[/td][td] [/td][td][/td][td] [/td][/tr]
[tr][td]

14​

[/td][td] soybeans[/td][td] Legumes[/td][td] [/td][td][/td][td] [/td][/tr]
[tr][td]

15​

[/td][td] lentil flour[/td][td] Legumes[/td][td] [/td][td][/td][td] [/td][/tr]
[tr][td]

16​

[/td][td] broccoli[/td][td] vegetable[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

17​

[/td][td] potato[/td][td] vegetable[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

The formula, that is,

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

confirmed with control+shift+enter (not just with enter) and copied down in column E, delivers the above exhibit. (Note. Sort A:B on A.)

 

[jgopalk]

This is great now. Really Great.

Only one thing remaining is "NA". Row number 5 (finger) and 10 (fofu). These two keywords are not found in A:B. Which should be reflect as "NA" in formula. Kindly suggest to do this alone.

I really happy and appreciate your help in this.

Thanks,
Gopal

 

[Aladin Akyurek]

This is great now. Really Great.

Only one thing remaining is "NA". Row number 5 (finger) and 10 (fofu). These two keywords are not found in A:B. Which should be reflect as "NA" in formula. Kindly suggest to do this alone.

I really happy and appreciate your help in this.

Thanks,
Gopal

The formula can only say something about what it looks for. It cannot say anything about an item that is in D2 but not in the list of column A.

 

[jgopalk]

Ya ok, Fine Aladin. Thanks you very much.

If not N/A, is there any possibility to mention that in the E column?

Thanks,
gopal

 

[jgopalk]

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