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

 

[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:

Ladies finger; Orange

Fruit & Vegetable

<tbody>
</tbody>

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 ?

1	lettuce | spinach | apple	vegetable & vegetable & & & & & & & & & & & & & & & & &
2	Brussels | zucchini	vegetable & vegetable & & & & & & & & & & & & & & & & &
3	celery	vegetable & & & & & & & & & & & & & & & & &
4	onion | fofu | soybeans	vegetable & Legumes & & & & & & & & & & & & & & & & &
5	lentil flour | broccoli	vegetable & Legumes & & & & & & & & & & & & & & & & &
6

<tbody>
</tbody>

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.

lettuce	vegetable
spinach	vegetable
Brussels	vegetable
zucchini	vegetable
celery	vegetable
onion	vegetable
soybeans	Legumes
lentil flour	Legumes
broccoli	vegetable
potato	vegetable

<colgroup><col><col></colgroup><tbody>
</tbody>

I hope you are asking about this master list.

Am I clear?

 

[Aladin Akyurek]

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

lettuce	vegetable
spinach	vegetable
Brussels	vegetable
zucchini	vegetable
celery	vegetable
onion	vegetable
soybeans	Legumes
lentil flour	Legumes
broccoli	vegetable
potato	vegetable

<tbody>
</tbody>

I hope you are asking about this master list.

Am I clear?

Row\Col	A​	B​	C​	D​	E​
1​
2​	Apple	Fruit		Apple | Orange | Banana	Fruit & Fruit & Fruit
3​	Orange	Fruit		Apple | Jasmine	Fruit & Flower
4​	Ladies finger	Vegetable		Carrot	Vegetable
5​	Carrot	Vegetable		Apple | finger	Fruit
6​	Banana	Fruit		Ladies finger; Orange	Fruit & Vegetable
7​	Jasmin	Flower		lettuce | spinach | apple	Fruit & vegetable & vegetable
8​	lettuce	vegetable		Brussels | zucchini	vegetable & vegetable
9​	spinach	vegetable		celery	vegetable
10​	Brussels	vegetable		onion | fofu | soybeans	vegetable & Legumes
11​	zucchini	vegetable		lentil flour | broccoli	Legumes & vegetable
12​	celery	vegetable
13​	onion	vegetable
14​	soybeans	Legumes
15​	lentil flour	Legumes
16​	broccoli	vegetable
17​	potato	vegetable
18​

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

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