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

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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)
tables.jpg
 

bilalsana

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
Is this what you are trying to achieve?

bilalsana.xlsm
ABCDEF
1Dinning 1Dinning 2Dinning 3Dinning 4Dinning 5
2Jan-01ApplePearPeachMango
3Jan-02BananaPear, Apple MangoPeachAppleMango
4Jan-03PearBananaPeachMangoApple
5
6
7
8Jan-01Jan-02Jan-03
9AppleDinning 1Dinning 2, Dinning 4Dinning 5
10Banana Dinning 1Dinning 2
11PearDinning 3Dinning 2Dinning 1
12PeachDinning 4Dinning 3Dinning 3
13MangoDinning 5Dinning 2, Dinning 5Dinning 4
Sheet1
Cell Formulas
RangeFormula
B9:D13B9=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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,337
Office Version
  1. 365
Platform
  1. Windows
This table should be in Sheet2 and reference to sheet1
bilalsana.xlsm
ABCDEF
1Dinning 1Dinning 2Dinning 3Dinning 4Dinning 5
2Jan-01ApplePearPeachMango
3Jan-02BananaPear, Apple MangoPeachAppleMango
4Jan-03PearBananaPeachMangoApple
Sheet1


Cell Formulas
RangeFormula
B2:D6B2=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

New Member
Joined
Oct 12, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
bilalsana.xlsm
ABCDEF
1Dinning 1Dinning 2Dinning 3Dinning 4Dinning 5
2Jan-01ApplePearPeachMango
3Jan-02BananaPear, Apple MangoPeachAppleMango
4Jan-03PearBananaPeachMangoApple
Sheet1


Cell Formulas
RangeFormula
B2:D6B2=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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top