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

 

[CyrusTheVirus]

You say

I will have maximum of two strings. One is “ | “ and next is “ ; ”.

Could you please clarify what you mean by that? Do you mean you will have three (not two) strings "Apple | Orange | Banana" or are you referring to 'Ladies finger' which is a two word keyword or are you referring to that you will have only two different type of string separators?

Also, I see a space before and after the "|" is there not a space before the ";" but not after it?

 

[jgopalk]

Thanks for your reply and sorry for the confusion.

Could you please clarify what you mean by that? Do you mean you will have three (not two) strings "Apple | Orange | Banana"

Here I might get multiple vertical bars ("|") or multiple semi colons (";") in a single cell. These types of special characters splits the words.

are you referring to 'Ladies finger' which is a two word keyword or are you referring to that you will have only two different type of string separators?

I might get "one word" or "two words" or multiple word keywords. There is no restriction in that. Just an example I have given these.

Also, I see a space before and after the "|" is there not a space before the ";" but not after it?

There will not any space between bars and semi colons (|, .

Thanks again for your questions. Hope I have clarified your doubts. Kindly check for any solution. There is no need to fix this by Vlook we may go with any other formula too. But kindly avoid Macros, which I am currently using.

Thank you.
Gopal

 

[azumi]

hope this works, try follow this link

https://simpan.ugm.ac.id/s/N9Si2NmUqt4jdEp

 

[Aladin Akyurek]

If you are on a 2016 system, try the following set up:

[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[/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][/td][td][/td][/tr]
[/table]

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

=TEXTJOIN(" & ",TRUE,IF(ISNUMBER(SEARCH($A$2:$A$7,$D2)),$B$2:$B$7,""))

 

[CyrusTheVirus]

Nice one Aladin.

 

[jgopalk]

Thanks all for your kind reply.

@Azumi: I could use your formula for my required. That is what i required. That is excellent Azumi. But, sheet is very slow when i using it in my system. I it is due to huge size of the formula? It would be great if you would reply for this.

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

Once again thanks all.

Gopal

 

[Aladin Akyurek]

[...]

@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">VBA</acronym> code to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

 

[jgopalk]

Thanks all for your reply and apologies for late reply. I was in limited net access area.

Formulas you have given is working good. But, when the different keywords comes in the list it should shows as N/A. For example, potato is not the list. So if comes in the Column D, result should shows as N/A.

Could you please check whether is this is possible to include in VBA/Macro code?

Thanks again.

Gopal

 

[Aladin Akyurek]

Thanks all for your reply and apologies for late reply. I was in limited net access area.

Formulas you have given is working good. But, when the different keywords comes in the list it should shows as N/A. For example, potato is not the list. So if comes in the Column D, result should shows as N/A.

...

How do you mean?