0

This is a question that I may have difficulty explaining, so bear with me.

On one sheet I have a column which has several cells, some of which are blank, some that have a single value, and some that have more than one value. On another sheet I have a list of those values with an appropriate home. Here’s an example of what I’m working with:

Sheet1:

C1: Team Names
C2: crimson-tide, bulldogs
C3: bulldogs
C4: (blank)
C5: canucks

Sheet2:

A1: Football          B1: Basketball        C1: Hockey
A2: crimson-tide      B2: celtics           C2: maple-leafs
A3: bulldogs          B3: mavericks         C3: oilers
A4: tigers            B4: gonzaga-bulldogs  C4: canucks
A5: longhorns         B5: warriors          C5: bruins

Expected Output on Sheet1:

D2: Football
D3: Football
D4: No Sport Found
D5: Hockey

Actual Output:

D2: (blank)
D3: FootballBasketBall
D4: (blank)
D5: Hockey

I am wanting to iterate through Sheet1!C2:C5 and any value that has a match on Sheet2!A2:C5 will display the appropriate header from Sheet2!A1:C1. For example, Sheet1!C2 would say ‘Football’ since either crimson-tide or bulldogs is under the football header on Sheet2!A1.

What may or may not be important to add: no values in Sheet1!C will have a team that will be in another header. Crimson-tide will only be under the football header on Sheet2, not both the football and basketball header, for example. So far I’ve only been able to show a True/False result if the team in Column C matches a team on Sheet2. I cannot figure out how to display the appropriate header. Please help!

Improve this question

1 Answer 1

Reset to default
0

Try to enter the following formula in cell D2

enter image description here

=LET(
     _S2Sports, Sheet2!A$2:C$5,
     _Sports, TOCOL(IFS(_S2Sports<>"",Sheet2!A$1:C$1),2,1),
     _Teams, TOCOL(_S2Sports,1,1),
     _Match, XLOOKUP(TEXTSPLIT(C2,,", "),_Teams,_Sports,"No Team Found"),
     IFERROR(TEXTJOIN("|",1,UNIQUE(_Match)),"No Sport Found"))

Using a single dynamic array formula :

=LET(
     _S2Sports, Sheet2!A$2:C$5,
     _Sports, TOCOL(IFS(_S2Sports<>"",Sheet2!A$1:C$1),2,1),
     _Teams, TOCOL(_S2Sports,1,1),
     BYROW(C2:C12, LAMBDA(x, LET(_Match, XLOOKUP(TEXTSPLIT(x,,", "),_Teams,_Sports,"No Team Found"),
     IFERROR(TEXTJOIN("|",1,UNIQUE(_Match)),"No Sport Found")))))

If you don't have access to TOCOL() & TEXTSPLIT() then:

enter image description here

=LET(
     _Sports, Sheet2!$A$2:$C$5,
     _Found, N(ISNUMBER(SEARCH(", "&_Sports&", ",", "&$C2&", "))),
     _MatrixCal, MMULT(SEQUENCE(,ROWS(_Sports))^0,_Found),
     _Output, FILTER(Sheet2!$A$1:$C$1,_MatrixCal,"No Team Found"),
     IF(C2="","No Sport Found",TEXTJOIN("|",,_Output)))
Improve this answer
13
  • Thank you for the fast reply! The first and last formula didn’t work for me, however the CONCAT formula is showing some promise. It seems on multiple values (crimson-tide, bulldogs) that it’s not providing a Team Name. However a single value (warriors) is showing the name as expected. I’ve also noticed that for teams that share a word, for example, gonzaga-bulldogs and georgia-bulldogs, it will show both Football and Basketball because both teams share the “bulldogs” text. Even though the teams are unique. Any idea on how all of that may be alleviated?
    – chunky-coal
    Commented Mar 1 at 3:03
  • @chunky-coal can you show me few more examples with the expected output, it will definitely help me understand also I can see clearly for multiple values like crimson-tide, bulldogs it is returning correct output. Yes it will give you one output only because i have unique function here. For the other two bulldogs i am not able to understand if you share an excel or a screenshot it will help me more to understand to help you out
    – Mayukh Bhattacharya
    Commented Mar 1 at 10:59
  • Of course. I just made some edits and hopefully that will make it easier to understand. Thank you!
    – chunky-coal
    Commented Mar 1 at 15:13
  • @chunky-coal hello can you try this =IFERROR(CONCAT(UNIQUE(REPT(Sheet2!$A$1:$C$1,N(IFNA(TEXTSPLIT(C2,,", ")=Sheet2!$A$2:$C$5,0))))),"No Sport Hockey")
    – Mayukh Bhattacharya
    Commented Mar 1 at 15:38
  • 1
    Thank you so much!! This worked as expected. Sorry for the delay. I have marked this answer as resolved and you are a hero.
    – chunky-coal
    Commented Mar 7 at 4:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .