lookup on cells with Comma separated value

H

huat8888

New Member
Joined
Nov 23, 2018
Messages
7
Hi All,


Need a help please with either using excel formula or Excel VBA to lookup cells with comma separated values.
I have 2 tables below: table 1 and table 2. I will like the Col B (2nd column) of Table 1 to return a true or yes when any of the value is in the Col A (first column) of table 2 are found in that cell.

Your advise here will be greatly appreciated.


Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A: Cells to search[/TD]
[TD]Col B:column to return lookup[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple, banana, orange, grape[/TD]
[TD]yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cherry, apple, tomato[/TD]
[TD]yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lettuce, strawberry, durian[/TD]
[TD]no[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cucumber, grape[/TD]
[TD]yes[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col A: Items[/TD]
[/TR]
[TR]
[TD]grape[/TD]
[/TR]
[TR]
[TD]apple[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Regards,
Lawrence
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
[TABLE="width: 1185"]
<colgroup><col><col><col span="12"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col A: Cells to search
[/TD]
[TD]Col B:column to return lookup[/TD]
[TD]my formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple, banana, orange, grape[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD]#####[/TD]
[TD][/TD]
[TD]row 21[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cherry, apple, tomato[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]lettuce, strawberry, durian[/TD]
[TD]no[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]cucumber, grape[/TD]
[TD]yes[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 2 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Col A: Items
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]grape[/TD]
[TD]row 30[/TD]
[TD][/TD]
[TD]######[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]apple[/TD]
[TD]row 31[/TD]
[TD][/TD]
[TD="colspan: 9"]=IF(ISERROR(SEARCH($A$30,A21)),IF(ISERROR(SEARCH($A$31,A21)),"no","yes"),"yes")[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Hi,

This will do what you asked, you can have as many items in your Table 2 as you want.
Notice my sample in A7 where the data includes "pineapple", the result is Correct as No even thou "apple" is in the list in Table 2:


Book1
AB
1Table 1
2Col A: Cells to searchCol B:column to return lookup
3apple, banana, orange, grapeYes
4cherry, apple, tomatoYes
5lettuce, strawberry, durianNo
6cucumber, grapeYes
7banana, pineappleNo
8
9
10Table 2
11Col A: Items
12grape
13apple
14cherry
15watermelon
Sheet513
Cell Formulas
RangeFormula
B3=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&A$12:A$15&","," "&A3&","))),"Yes","No")
 
Upvote 0
oldbrewer ,

I think your formula works for 2 cells (row 30 and row 31) only?. If there is more data, we will have to length the formula adding in additional search() formula?
i appreciate you sharing your formula and thank you!
 
Upvote 0
jtakw said:
Hi,

This will do what you asked, you can have as many items in your Table 2 as you want.
Notice my sample in A7 where the data includes "pineapple", the result is Correct as No even thou "apple" is in the list in Table 2:

AB
Table 1
Col A: Cells to searchCol B:column to return lookup
apple, banana, orange, grapeYes
cherry, apple, tomatoYes
lettuce, strawberry, durianNo
cucumber, grapeYes
banana, pineappleNo
Table 2
Col A: Items
grape
apple
cherry
watermelon

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

</tbody>
Sheet513​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B3[/TH]
[TD="align: left"]=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&A$12:A$15&","," "&A3&","))),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

Thank you jtakw,

Your formula is very helpful. Will you explain to me how does this formula add up to function to what is needed please?


 
Upvote 0
huat8888 said:
Thank you jtakw,
Your formula is very helpful. Will you explain to me how does this formula add up to function to what is needed please?
Click to expand...

You're welcome.

The LOOKUP is looking for the value of 2 within the SEARCH results, we divide 1 by the search results, thereby ensuring the result will Never be Greater than 1, since LOOKUP can Not find 2, it returns, if any, the Last found value by the SEARCH.

This can also be accomplished by using the "Big Num" ( 9.99999999999999E+307 ) in place of 2, and do away with the 1/ part.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Textjoin and Filter using VBA
Replies
7
Views
192
BritsBlitz
B
E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
4K
excelnewb221
E
E
  • Solved
Index/Match - Return value if partial text from cell contained in separate column
2
Replies
16
Views
3K
Kevin_Li
Kevin_Li
C
  • Solved
Xlookup to Find a Value in 2 Columns
Replies
3
Views
498
Fluff
Fluff
S
  • Solved
Lookup results in a table based on multiple criteria and set number of occurrences
Replies
4
Views
330
kevin9999
K

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top