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]

use XL2BB to post your example, nobody want to retype your data from the picture

 

[sandy666]

anyway, your expected result is not correct

 

[bilalsana]

View attachment 24091
Like this

anyway, your expected result is not correct

use XL2BB to post your example, nobody want to retype your data from the picture

Sorry, New here. will do that.

 

[sandy666]

No problem, but post whole expected result not a part only and source data also

 

[bilalsana]

I don't understand, how's the expected result not correct?

 

[sandy666]

source:
Jan-02 and Dinning 2 contain Pear, Apple, Mango
Result
Jan-02 contain Pear for Dinning 2 only

 

[bilalsana]

Book1.xlsx
	B
9	Dinning 1
Sheet1

Cell Formulas
Range	Formula
B9	B9	=TEXTJOIN(", ",TRUE,IF($A$2:$A$4=B$8,IF($B$2:$F$4=$A9,$B$1:$F$1,""),""))

Anyywas, here is my solution but it breaks with comma seperated values

 

[bilalsana]

Book1.xlsx
	A	B	C	D	E	F
1		Dinning 1	Dinning 2	Dinning 3	5	Dinning 4
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	5	Dinning 4
10	Banana
11	Pear	Dinning 3
12	Peach
13	Mango
Sheet1

Cell Formulas
Range	Formula
C9:D9,B9:B11	C9	=TEXTJOIN(", ",TRUE,IF($A$2:$A$4=C$8,IF($B$2:$F$4=$A9,$B$1:$F$1,""),""))

 

[sandy666]

create expected result manually
or choose one

source						Result1
Date	Dinning 1	Dinning 2	Dinning 3	Dinning 4		Fruit	01/01/2001	01/01/2002	01/01/2003
01/01/2001	Apple	Banana	Pear, Peach, Plum	Mango		Apple	Dinning 1		Dinning 4
01/01/2002	Banana	Pear	Peach, Plum, Apple	Mango		Banana	Dinning 2	Dinning 1	Dinning 2
01/01/2003	Pear	Banana	Peach, Mango	Apple		Mango	Dinning 4	Dinning 4
						Peach, Mango			Dinning 3
						Peach, Plum, Apple		Dinning 3
						Pear		Dinning 2	Dinning 1
						Pear, Peach, Plum	Dinning 3
						Result2
						Fruit	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
						Plum	Dinning 3	Dinning 3

 

[Peter_SSs]

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))),""))