I'm fairly new to Excel so I hope you can please help me out!
For example, if I have this:
| Employee | 1 | 2 | 3 |
|---|---|---|---|
| 12:00 | Room 1 | Room 3 | Room 2 |
| 14:00 | Room 1 | Room 3 | Room 3 |
| 16:00 | Room 1 | Room 1 | Room 2 |
So, in this case the result I want should just be:
| Room 1 | Employee |
|---|---|
| 12:00 | 1 |
| 14:00 | 1 |
| 16:00 | 1 / 2 |
So far, I've used a bunch of IF functions to examine each column for every row in my dataset. I think I need to use TEXTJOIN but I don't know how to apply it in this case.
I would like to thank you beforehand for any help you can give me!
CodePudding user response：
Yes you need TEXTJOIN but use a FILTER with INDEX/MATCH to return the correct row:
=TEXTJOIN("/",,FILTER($B$1:$D$1,INDEX($B$2:$D$4,MATCH(G2,$A$2:$A$4,0),0)=$G$1))
