lookup on cells with Comma separated value

[huat8888]

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

Col A: Cells to search	Col B:column to return lookup
apple, banana, orange, grape	yes
cherry, apple, tomato	yes
lettuce, strawberry, durian	no
cucumber, grape	yes

<tbody>
</tbody>

Table 2

Col A: Items

grape

apple

<tbody>
</tbody>

Regards,
Lawrence

 

[oldbrewer]

Table 1

Col A: Cells to search

Col B:column to return lookup

my formula

apple, banana, orange, grape

yes

yes

#####

row 21

cherry, apple, tomato

yes

yes

lettuce, strawberry, durian

no

no

cucumber, grape

yes

yes

Table 2

Col A: Items

grape

row 30

######

apple

row 31

=IF(ISERROR(SEARCH($A$30,A21)),IF(ISERROR(SEARCH($A$31,A21)),"no","yes"),"yes")

<colgroup><col><col><col span="12"></colgroup><tbody>
</tbody>

 

[jtakw]

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
	A	B
1	Table 1
2	Col A: Cells to search	Col B:column to return lookup
3	apple, banana, orange, grape	Yes
4	cherry, apple, tomato	Yes
5	lettuce, strawberry, durian	No
6	cucumber, grape	Yes
7	banana, pineapple	No
8
9
10	Table 2
11	Col A: Items
12	grape
13	apple
14	cherry
15	watermelon
Sheet513

Cell Formulas
Range	Formula
B3	=IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&A$12:A$15&","," "&A3&","))),"Yes","No")

 

[huat8888]

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!

 

[huat8888]

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:

	A	B
1	Table 1
2	Col A: Cells to search	Col B:column to return lookup
3	apple, banana, orange, grape	Yes
4	cherry, apple, tomato	Yes
5	lettuce, strawberry, durian	No
6	cucumber, grape	Yes
7	banana, pineapple	No
8
9
10	Table 2
11	Col A: Items
12	grape
13	apple
14	cherry
15	watermelon

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet513​

Worksheet Formulas Cell Formula B3 =IF(ISNUMBER(LOOKUP(2,1/SEARCH(" "&A$12:A$15&","," "&A3&","))),"Yes","No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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?

 

[oldbrewer]

yes it is meant to work on 2 only you can drag it down any number of rows

 

[jtakw]

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?

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.