Since few hours I am stuck on a specific excel case ...
To summarize: I have different kind of category:
- Category A: Math and physics
- Category B: Math and Music
- Category C: Physic and Sport
- Category D: Sport and Music
The result on my excel file:
| Title | Math | Physic | Sport | Music | Category |
|---|---|---|---|---|---|
| John | X | X | X | A, B | |
| Kate | X | X | A | ||
| Steven | X | X | D | ||
| Bart | X | X | X | X | A, B, C, D |
How can I have the the different category on the profile ? It can have 1 or several categories at the same time.
I created this formula but it match only one category and I would like to have all the possibility...
=IF(AND(A1="X",B1="X"),"CategoryA",IF(AND(A1="X",D4="X"),"CategoryC",IF(AND(C1="X",B1="X"),"CategoryC",IF(AND(C1="X",D1="X"),"CategoryD","No Profile"))))
Thanks a lot ...
CodePudding user response：
With Excel 365:
Formula in F2:
=TEXTJOIN(", ",,FILTER({"A","B","C","D"},ISNUMBER(SEARCH({"XX??","X??X","?XX?","??XX"},CONCAT(IF(B2:E2=""," ",B2:E2)))),"No Profile"))
Or the fancy way using BYROW() to spill results:
=BYROW(B2:E5,LAMBDA(x,TEXTJOIN(", ",,FILTER({"A","B","C","D"},ISNUMBER(SEARCH({"XX??","X??X","?XX?","??XX"},CONCAT(IF(x=""," ",x)))),"No Profile"))))
