How do I search a row for a specific text and return with the column it is in, as in this example

[bilalsana]

I have a sheet for distributing fruits based on the date, making sure they are not repeated in the different halls on the same date, as seen in the picture, I want to see the table below so that I know where that fruit is being served on that day. Importantly the new table would be in Sheet2 (unlike in the example)

 

[sandy666]

like this?

Value	01/01/2001	01/01/2002	01/01/2003
Apple	Dinning 1	Dinning 3	Dinning 4
Banana	Dinning 2	Dinning 1	Dinning 2
Mango	Dinning 4	Dinning 4	Dinning 3
Peach	Dinning 3	Dinning 3	Dinning 3
Pear	Dinning 3	Dinning 2	Dinning 1

 

[bilalsana]

like this?

Value	01/01/2001	01/01/2002	01/01/2003
Apple	Dinning 1	Dinning 3	Dinning 4
Banana	Dinning 2	Dinning 1	Dinning 2
Mango	Dinning 4	Dinning 4	Dinning 3
Peach	Dinning 3	Dinning 3	Dinning 3
Pear	Dinning 3	Dinning 2	Dinning 1

Yes exactly. But some cells have multiple fruit listed like: Apple,Pear, Banana

 

[sandy666]

Yes exactly. But some cells have multiple fruit listed like: Apple,Pear, Banana

so exactly or show expected result based on your example

 

[bilalsana]

As in the above example, if column B has "Apple,Banana" on Jan-01, I would like it to return Dinning 1 in both B9 and B10

 

[bilalsana]

As in the above example, if column B has "Apple,Banana" on Jan-01, I would like it to return Dinning 1 in both B9 and B10

Ofcourse there would be no doubling, as in this example. 1 entry would only come once in a row. Dinning 2 would not have Banana in it, unlike in the example

 

[sandy666]

???

Value	01/01/2001	01/01/2002	01/01/2003
Apple	Dinning 1		Dinning 4
Banana	Dinning 2	Dinning 1	Dinning 2
Mango	Dinning 4	Dinning 4
Peach, Apple		Dinning 3
Peach, Mango			Dinning 3
Pear		Dinning 2	Dinning 1
Pear, Peach	Dinning 3

 

[bilalsana]

Yes. But will it scan fir more than 2 items in the same cell (seperated by commas)?

 

[sandy666]

like this?

Value	01/01/2001	01/01/2002	01/01/2003
Apple	Dinning 1		Dinning 4
Banana	Dinning 2	Dinning 1	Dinning 2
Mango	Dinning 4	Dinning 4
Peach, Mango			Dinning 3
Peach, Plum, Apple		Dinning 3
Pear		Dinning 2	Dinning 1
Pear, Peach, Plum	Dinning 3

 

[bilalsana]

like this?

Value	01/01/2001	01/01/2002	01/01/2003
Apple	Dinning 1		Dinning 4
Banana	Dinning 2	Dinning 1	Dinning 2
Mango	Dinning 4	Dinning 4
Peach, Mango			Dinning 3
Peach, Plum, Apple		Dinning 3
Pear		Dinning 2	Dinning 1
Pear, Peach, Plum	Dinning 3

Like this