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)

 

[bilalsana]

Is this what you are trying to achieve?

bilalsana.xlsm
	A	B	C	D	E	F
1		Dinning 1	Dinning 2	Dinning 3	Dinning 4	Dinning 5
2	Jan-01	Apple		Pear	Peach	Mango
3	Jan-02	Banana	Pear, Apple Mango	Peach	Apple	Mango
4	Jan-03	Pear	Banana	Peach	Mango	Apple
5
6
7
8		Jan-01	Jan-02	Jan-03
9	Apple	Dinning 1	Dinning 2, Dinning 4	Dinning 5
10	Banana		Dinning 1	Dinning 2
11	Pear	Dinning 3	Dinning 2	Dinning 1
12	Peach	Dinning 4	Dinning 3	Dinning 3
13	Mango	Dinning 5	Dinning 2, Dinning 5	Dinning 4
Sheet1

Cell Formulas
Range	Formula
B9:D13	B9	=TEXTJOIN(", ",1,FILTER($B$1:$F$1,ISNUMBER(SEARCH(" "&$A9&" ",INDEX(SUBSTITUTE(" "&$B$2:$F$4&" ",","," "),MATCH(B$8,$A$2:$A$4,0),0))),""))

Exactly! Just one lst thing, This table should be in Sheet2 and reference to sheet1

 

[Peter_SSs]

This table should be in Sheet2 and reference to sheet1

bilalsana.xlsm
	A	B	C	D	E	F
1		Dinning 1	Dinning 2	Dinning 3	Dinning 4	Dinning 5
2	Jan-01	Apple		Pear	Peach	Mango
3	Jan-02	Banana	Pear, Apple Mango	Peach	Apple	Mango
4	Jan-03	Pear	Banana	Peach	Mango	Apple
Sheet1

bilalsana.xlsm
	A	B	C	D
1		Jan-01	Jan-02	Jan-03
2	Apple	Dinning 1	Dinning 2, Dinning 4	Dinning 5
3	Banana		Dinning 1	Dinning 2
4	Pear	Dinning 3	Dinning 2	Dinning 1
5	Peach	Dinning 4	Dinning 3	Dinning 3
6	Mango	Dinning 5	Dinning 2, Dinning 5	Dinning 4
Sheet2

Cell Formulas
Range	Formula
B2:D6	B2	=TEXTJOIN(", ",1,FILTER(Sheet1!$B$1:$F$1,ISNUMBER(SEARCH(" "&$A2&" ",INDEX(SUBSTITUTE(" "&Sheet1!$B$2:$F$4&" ",","," "),MATCH(B$1,Sheet1!$A$2:$A$4,0),0))),""))

 

[bilalsana]

bilalsana.xlsm
	A	B	C	D	E	F
1		Dinning 1	Dinning 2	Dinning 3	Dinning 4	Dinning 5
2	Jan-01	Apple		Pear	Peach	Mango
3	Jan-02	Banana	Pear, Apple Mango	Peach	Apple	Mango
4	Jan-03	Pear	Banana	Peach	Mango	Apple
Sheet1

bilalsana.xlsm
	A	B	C	D
1		Jan-01	Jan-02	Jan-03
2	Apple	Dinning 1	Dinning 2, Dinning 4	Dinning 5
3	Banana		Dinning 1	Dinning 2
4	Pear	Dinning 3	Dinning 2	Dinning 1
5	Peach	Dinning 4	Dinning 3	Dinning 3
6	Mango	Dinning 5	Dinning 2, Dinning 5	Dinning 4
Sheet2

Cell Formulas
Range	Formula
B2:D6	B2	=TEXTJOIN(", ",1,FILTER(Sheet1!$B$1:$F$1,ISNUMBER(SEARCH(" "&$A2&" ",INDEX(SUBSTITUTE(" "&Sheet1!$B$2:$F$4&" ",","," "),MATCH(B$1,Sheet1!$A$2:$A$4,0),0))),""))

Exactly what I need. Can't thank you enough!

 

[Peter_SSs]

Can't thank you enough!

You just did.
You're welcome.