VLOOKUP multiple cells on column

[yshmelev]

Hi Community! 

I'm struggling with next task: I would like to identify if any of multiple cells (Fruit 1 through Fruit 3) in each row from Table 2 contains in column Fruits of Table1.

Fruits

apple

orange

grape

melon

Name	Fruit 1	Fruit 2	Fruit 3	Contains Fruits Table 2?
Mike	apple			Yes
Bob	peach	pear	orange	Yes
Jack	banana			No
Rob	peach	banana		No
Rita	apple	orange	banana	Yes

Fruits in Table 2 can be up to 40 columns. Number of rows in Table1 is about 300.

I hope it is understandable, and someone can help me resolve this.

I really appreciate the support in advance!

[brandon_bunney · Accepted solution]

Hello,

Attached is a workflow that correctly produced the fruit matches in rows 1, 2 and 5 based on your example datasets. I believe it would continue to work with a dataset featuring more "Fruit #" columns if you check the additional checkboxes in the Transpose tool as suggested in the workflow comment. I didn't include "Name" column in the output since we weren't comparing the Fruits to the Names. 

[Qiu · Accepted solution]

@yshmelev 
Tried something different.

[yshmelev]

Thanks a lot! That's very useful!

[yshmelev]

Thank you! That's exactly what I was looking for!