If formula with differing search ranges

[AndyC83]

Hi I'm trying to get something to work which may be much easier than i'm making it for myself. I've been trying combinations of IF/AND/INDEX/MATCH and not getting the results I need.

Column A has a list of references which can and should contain duplicates as below Column B contains varying text:
1111 Football player
1111 Hockey player
1122 Hockey manager
1123 Football manager
1234 Basketball coach
1234 Football player


I need a formula in column C that tells me if the reference in Column A says "Football" then TRUE or not FALSE (i dont care if it says manager or player only that it contains the word "football"), but i need it to apply to each row with the same reference for example rows 1&2 would say yes because the reference 1111 contains Football in row 1. How would I do this without using VBA (the real report can't contain VBA due to client restrictions).

Thank you in advance for any help.

 

[Automatrix]

Keep in mind that this solution is case sensitive, so it won't find "football". There are ways to fix that if needed.

Book5
	A	B	C
1	1111	Football player	TRUE
2	1111	Hockey player	TRUE
3	1122	Hockey manager	FALSE
4	1123	Football manager	TRUE
5	1234	Basketball coach	TRUE
6	1234	Football player	TRUE
Sheet2

Cell Formulas
Range	Formula
C1:C6	C1	=COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,"*"&"Football"&"*")>0

 

[AndyC83]

Keep in mind that this solution is case sensitive, so it won't find "football". There are ways to fix that if needed.

Book5
	A	B	C
1	1111	Football player	TRUE
2	1111	Hockey player	TRUE
3	1122	Hockey manager	FALSE
4	1123	Football manager	TRUE
5	1234	Basketball coach	TRUE
6	1234	Football player	TRUE
Sheet2

Cell Formulas
Range	Formula
C1:C6	C1	=COUNTIFS($A$1:$A$6,A1,$B$1:$B$6,"*"&"Football"&"*")>0

Hi yes when adapted to my spreadsheet that works but to make it none case sensitive what would need to change as the real spreadsheet its referencing a free type field so some people using capitals some not.

 

[Automatrix]

Hi yes when adapted to my spreadsheet that works but to make it none case sensitive what would need to change as the real spreadsheet its referencing a free type field so some people using capitals some not.

Actually I just double checked it - it is NOT case sensitive. That's helpful in your case, but not if you need it to be case sensitive for some reason.

 

[AndyC83]

Actually I just double checked it - it is NOT case sensitive. That's helpful in your case, but not if you need it to be case sensitive for some reason.

amazing man, thank you so much