Not sure what function to use here: IF OR AND INDEX MATCH?

[slam]

Hi gang! Hope everyone is having an amazing day.

Mock scenario to mirror what I'm looking for:

I have two worksheets, Sheet1, Sheet2.

Sheet1 has a list of teams in column A where each team is listed only once:

Team1
Team2
Team3
Team4

Sheet2 lists the roles within the teams where the team name is in column A again, and the role is in column B. Example:

Team1 | Expert-J
Team1 | Manager
Team1 | Programmer
Team2 | Manager
Team3 | Expert-J
Team3 | Expert
Team4 | Assessor
Team4 | Expert

What I am looking for is on Sheet1, I need the following displayed based on the above values:

Team1 | Expert & Non-Expert
Team2 | Non-Expert
Team3 | Expert
Team4 | Expert & Non-Expert

In other words, I need to look for the word Expert on Sheet2 within each Team, and see if it's the only role on a team (display "Expert"), if there's no instances of the word (display "Non-Expert"), or if there's an Expert along with other roles (display "Expert & Non-Expert")

Massively appreciate any help on this! <3

 

[Eric W]

This should work for Excel 365:

Book1
	A	B
1	Teams
2	Team1	Expert & Non-expert
3	Team2	Non-expert
4	Team3	Expert
5	Team4	Expert & Non-expert
6
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LET(ex,COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"*expert*"),ne,COUNTIF(Sheet2!A:A,A2)-ex,IF(ex>0,"Expert","")&IF(AND(ex>0,ne>0)," & ","")&IF(ne>0,"Non-expert",""))

I'll see about getting something for 2019.

 

[Eric W]

This works in 2019:

=TEXTJOIN(" & ",1,CHOOSE({1,2},IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"*expert*"),"Expert",""),IF(COUNTIF(Sheet2!A:A,A2)-COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"*expert*"),"Non-expert","")))

 

[JamesCanale]

365 only

MrExcelPlayground13.xlsx
	A	B	C	D
1	Sheet 1			Sheet 2
2	Team1	Team1 | Expert & Non-Expert		Team1 | Expert-J
3	Team2	Team2 | Non-Expert		Team1 | Manager
4	Team3	Team3 | Expert		Team2 | Programmer
5	Team4	Team4 | Expert & Non-Expert		Team2 | Manager
6				Team3 | Expert-J
7				Team3 | Expert
8				Team4 | Assessor
9				Team4 | Expert
Sheet16

Cell Formulas
Range	Formula
B2:B5	B2	=LET(a,A2,b,$D$2:$D$9,c,NOT(ISERR(SEARCH(a,b))),d,FILTER(b,c),e,ISERR(SEARCH("Expert",d)),f,SUM(--e)+1,a&" | "&CHOOSE(f,"Expert","Expert & Non-Expert","Non-Expert"))

 

[slam]

This should work for Excel 365:

Book1
	A	B
1	Teams
2	Team1	Expert & Non-expert
3	Team2	Non-expert
4	Team3	Expert
5	Team4	Expert & Non-expert
6
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LET(ex,COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"*expert*"),ne,COUNTIF(Sheet2!A:A,A2)-ex,IF(ex>0,"Expert","")&IF(AND(ex>0,ne>0)," & ","")&IF(ne>0,"Non-expert",""))

I'll see about getting something for 2019.

Thank you so much, initial checks show this works perfectly!