How to Select a value separated by ; in a cell to lookup

[Jainanki]

Hi,

this has been driving me crazy and i am unable to find a formula to drive me the result. Please refer to the below case.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]234563; 234575; 564543[/TD]
[TD]Apple[/TD]
[TD]Yes[/TD]
[TD]234575[/TD]
[TD](Apple)[/TD]
[/TR]
[TR]
[TD]223344; 278923; 232324[/TD]
[TD]mango[/TD]
[TD]No[/TD]
[TD]232324[/TD]
[TD](Mango)[/TD]
[/TR]
[TR]
[TD]123456; 567890[/TD]
[TD]banana[/TD]
[TD]Yes[/TD]
[TD]123456[/TD]
[TD](Banana)[/TD]
[/TR]
</tbody>[/TABLE]

How can get the values in column E i.e. apple, mango and banana using D1 as the lookup array. First challenge is to derive the value in D from cell A and then lookup.

Please note that split cells wont work as there can be instances where i could have more than 200 numbers in a cell.

Appreciate your swift response on this.

Regards,
Ankit Jain

 

[Weazel]

if i'm understanding correctly maybe something like....

E1="("&LOOKUP(9.99E+307,SEARCH(D1,$A$1:$A$3),$B$1:$B$3)&")"

[TABLE="class: grid"]
<tbody>[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]234563; 234575; 564543[/TD]
[TD]Apple[/TD]
[TD]Yes[/TD]
[TD]

234575​

[/TD]
[TD](Apple)[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]223344; 278923; 232324[/TD]
[TD]mango[/TD]
[TD]No[/TD]
[TD]

232324​

[/TD]
[TD](mango)[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]123456; 567890[/TD]
[TD]banana[/TD]
[TD]Yes[/TD]
[TD]

123456​

[/TD]
[TD](banana)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

Here's one way

Excel 2010
	A	B	C	D	E	F
1	234563; 234575; 564543	Apple	Yes	234575	(Apple)	Apple
2	223344; 278923; 232324	mango	No	232324	(Mango)	mango
3	123456; 567890	banana	Yes	123456	(Banana)	banana
Sheet1

Cell Formulas
Range	Formula
F1	=INDEX($B$1:$B$3,MATCH("*"&D1&"*",$A$1:$A$3,0))

 

[Eric W]

I have a version very similar to Comfy's:

	A	B	C	D	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]234563; 234575; 564543[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"]234575[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]223344; 278923; 232324[/TD]
[TD="bgcolor: #FAFAFA"]mango[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"]232324[/TD]
[TD="bgcolor: #FAFAFA"]mango[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]123456; 567890[/TD]
[TD="bgcolor: #FAFAFA"]banana[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"]123456[/TD]
[TD="bgcolor: #FAFAFA"]banana[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]123[/TD]
[TD="bgcolor: #FAFAFA"]banana[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=IF(D2="","",INDEX($B$2:$B$20,MATCH("*"&D2&"*",$A$2:$A$20,0)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



I added an IF at the start that would leave an empty cell if the lookup value is empty. Also, look at line 5. 123 ends up matching with line 3, because 123 is contained within 123456. If you think that might be an issue with your full data, then we'd need to look for a separator between values. Will there always be a semicolon/space between values?

 

[Jainanki]

Hi Eric,

there always be a ; after 6 digit number. How i would like to select the absolute number not the match as you highlighted.

there are three challenges i have faced here and need your assistance in that,

If the name number is twice or X number of time in a column, the lookup value can be different depending on the number of line items. Is it possible to get all results separated by , or ; in case of duplication of lookup array in a column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]lookup array[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]ID[/TD]
[TD]Result required[/TD]
[TD]Result required[/TD]
[/TR]
[TR]
[TD]123456;456789[/TD]
[TD]Mango[/TD]
[TD]Yes[/TD]
[TD]456745[/TD]
[TD]Guava[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]232323;242424;456789[/TD]
[TD]Banana[/TD]
[TD]No[/TD]
[TD]123456[/TD]
[TD]Mango; Guava[/TD]
[TD]Yes; No[/TD]
[/TR]
[TR]
[TD]456745;343456;123456[/TD]
[TD]Guava[/TD]
[TD]No[/TD]
[TD]898989[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]898989;232323[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]232323[/TD]
[TD]Banana; [/TD]
[TD]No; Yes[/TD]
[/TR]
</tbody>[/TABLE]


Appreciate your help in this.

 

[Jainanki]

Appreciate your response on this thread.

Just slight modification,there always be a ; after 6 digit number. How i would like to select the absolute number not the match as you highlighted.

there are three challenges i have faced here and need your assistance in that,

If the name number is twice or X number of time in a column, the lookup value can be different depending on the number of line items. Is it possible to get all results separated by , or ; in case of duplication of lookup array in a column.

[TABLE="width: 500"]
<tbody>[TR]
[TD]lookup array[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]ID[/TD]
[TD]Result required[/TD]
[TD]Result required[/TD]
[/TR]
[TR]
[TD]123456;456789[/TD]
[TD]Mango[/TD]
[TD]Yes[/TD]
[TD]456745[/TD]
[TD]Guava[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]232323;242424;456789[/TD]
[TD]Banana[/TD]
[TD]No[/TD]
[TD]123456[/TD]
[TD]Mango; Guava[/TD]
[TD]Yes; No[/TD]
[/TR]
[TR]
[TD]456745;343456;123456[/TD]
[TD]Guava[/TD]
[TD]No[/TD]
[TD]898989[/TD]
[TD][/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]898989;232323[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]232323[/TD]
[TD]Banana; [/TD]
[TD]No; Yes[/TD]
[/TR]
</tbody>[/TABLE]


Appreciate your help in this.

 

[Eric W]

Before I create some formulas, let me give you some options. To give you the output as you format it, that would probably require writing a VBA function, or an on-demand macro. Neither would be too difficult, it just depends on whether you want VBA code in your workbook.

Second option would be to reformat the output, something like this:

	A	B	C	D	E	F	G	H	I	J
Mango, Yes	Guava, No	Grape, No
, Yes

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]lookup array[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ID[/TD]
[TD="bgcolor: #FAFAFA"]Result required[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]123456;456789[/TD]
[TD="bgcolor: #FAFAFA"]Mango[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]456745[/TD]
[TD="bgcolor: #FAFAFA"]Guava, No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]232323;242424;456789[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]123456[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]456745;343456;123456[/TD]
[TD="bgcolor: #FAFAFA"]Guava[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]898989[/TD]
[TD="bgcolor: #FAFAFA"], Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]898989;232323[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]232323[/TD]
[TD="bgcolor: #FAFAFA"]Banana, No[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]123456;987654[/TD]
[TD="bgcolor: #FAFAFA"]Grape[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet16​

There are a few variations, but the main idea is that each additional match would go in the next column to the right.

Let me know what you think.

 

[Jainanki]

Hi Eric,

i am pretty good with getting a vba code in the workbook but in condition that it gives the mentioned output as i described above.

Thank you for reverting back. Appreciate your help.

Cheers,
Jainanki

 

[Eric W]

Here's a UDF that should do what you want:

1) Open your workbook
2) Right click on the sheet tab on the bottom and select View Code
3) From the menu, select Insert > Module
4) Paste the following code to the window that opens:

Public Function FindMatches(MyVal As String, MatchTable As Range, ResultTable As Range) As String
Dim MyTab, MyRes, FM As String, i As Long, wk As String

    FM = ""
    If MyVal = "" Then Exit Function
    
    MyTab = MatchTable.Value
    MyRes = ResultTable.Value
    
    For i = 1 To UBound(MyTab)
        wk = Replace(";" & MyTab(i, 1) & ";", " ", "")
        If InStr(wk, ";" & MyVal & ";") > 0 Then FM = FM & MyRes(i, 1) & "; "
    Next i
    
    If Len(FM) > 2 Then FM = Left(FM, Len(FM) - 2)
    FindMatches = FM
        
End Function

5) Press Alt-Q to close the VBA editor.
6) Your worksheet should look something like this:

	A	B	C	D	E	F	G	H
333333;898989;999999

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]lookup array[/TD]
[TD="bgcolor: #FAFAFA"]A[/TD]
[TD="bgcolor: #FAFAFA"]B[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]ID[/TD]
[TD="bgcolor: #FAFAFA"]Result A[/TD]
[TD="bgcolor: #FAFAFA"]Result B[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]123456;456789[/TD]
[TD="bgcolor: #FAFAFA"]Mango[/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]456745[/TD]
[TD="bgcolor: #FAFAFA"]Guava[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]232323;242424;456789[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="align: right"]123456[/TD]
[TD="bgcolor: #FAFAFA"]Mango; Guava; Grape[/TD]
[TD="bgcolor: #FAFAFA"]Yes; No; No[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]456745;343456;123456[/TD]
[TD="bgcolor: #FAFAFA"]Guava[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]898989[/TD]
[TD="bgcolor: #FAFAFA"]; Apple[/TD]
[TD="bgcolor: #FAFAFA"]Yes; Maybe[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]898989;232323[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Yes[/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]232323[/TD]
[TD="bgcolor: #FAFAFA"]Banana; [/TD]
[TD="bgcolor: #FAFAFA"]No; Yes[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA"]123456;987654[/TD]
[TD="bgcolor: #FAFAFA"]Grape[/TD]
[TD="bgcolor: #FAFAFA"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"]8989[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]7[/TD]

[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA"]Maybe[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA, align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[TD="bgcolor: #FAFAFA"][/TD]

</tbody>

Sheet16​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=FindMatches(F2,$A$2:$A$10,$B$2:$B$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H2[/TH]
[TD="align: left"]=FindMatches(F2,$A$2:$A$10,$C$2:$C$10)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Install the G2 and H2 functions. The 3 arguments are the lookup value, the range to search, and the range to return the results from. You can then drag them down the column.

If all the numbers are 6-digits, then we don't really need to worry about a partial match, as long as the list always uses a semicolon for a separator. But I checked for that condition anyway, which you can see on line 6.

Let me know how this works.

 

[Jainanki]

Hi Eric,

this looks totally promising but when i am entering this code in the module and hit Alt Q, but i dont see the formula being created while i am entering FindMatches.

Is there any reason why i don't see the function. I am using Office 365 and Office 2013.