Find duplicate between two ranges and report true or false.

[Doggson2008]

Hello!
I'm very fresh to this and trying to learn but i'm having real issues trying to get this to work.
I've tried multiple "countifs" and "if(sum"s and i'm getting nowhere.
I'm looking to see if ANY text in F range is the same as ANY in the H range, If so return true, otherwise false.
Nevermind the L and N range, Just to show what i'm after.

I would not mind just getting pushed on the right track, I enjoy learning but this is way above my brain right now .-(

Thanks! Dogg

 

[Fluff]

Hi & welcome to MrExcel.
How about

=SUM(COUNTIFS(F1:F8,H1:H8))>0

 

[Doggson2008]

=SUM(COUNTIFS(F1:F8,H1:H8))>0

Hi, And thank you.
Sadly that don't do it for me.

Even with everyhting in those matching eachother it still reports False

 

[Fluff]

Can you post some sample data, including the formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Doggson2008]

Can you post some sample data, including the formula.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Sure i will do that as soon as i can, Thanks.

 

[Doggson2008]

Hello! Finally back at work.
This is "roughly" what the sheet looks like, But it's huge and contains over 50 teams and 1500 unique names. It's too big to do this manually because it takes me days to sort.
What i want is the indicator boxes to fill with an "O" if it is false, An "X" if it is true. To get an overview of what Mix groups contains the different "colored teams", I hope this makes sense.

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2
3
4	Team Red	Team Blue	Team Green	Team Purple		Mix&Match A		Mix&Match B		Mix&Match C		Mix&Match D
5	Thor	Atlas	Reyal	Barney		Thor		Freya
6	Freya	Nova	Tommen	Uglük		Atlas		Lewi
7	Sigrid	Signe	Thorend	Loke		Uglük		Kole
8	Apollo	Lewi	Maximo	Sigmund		Eden		Thorend
9	Odhen	Eden	Stig	Vanheli		Apollo		Vanheli
10
11	=IFERROR(VLOOKUP($A$5:$A$9;$F$5:$F$9;1;0);"O")					Red?	##	Red?	##	Red?	##	Red?	##
12						Blue?	##	Blue?	##	Blue?	##	Blue?	##
13						Green?	##	Green?	##	Green?	##	Green?	##
14						Purple?	O	Purple?	O	Purple?	O	Purple?	O
15							Uglük		O		O		O
16							O		O		O		O
17							O		O		O		O
18							O		Vanheli		O		O
Blad1

Cell Formulas
Range	Formula
G11	G11	=IFERROR(VLOOKUP($A$5:$A$9,$F$5:$F$9,1,0),"O")
G12	G12	=IFERROR(VLOOKUP($B$5:$B$9,$F$5:$F$9,1,0),"O")
G13	G13	=IFERROR(VLOOKUP($C$5:$C$9,$F$5:$F$9,1,0),"O")
G14:G18	G14	=IFERROR(VLOOKUP($D$5:$D$9,$F$5:$F$9,1,0),"O")
I11	I11	=IFERROR(VLOOKUP($A$5:$A$9,$H$5:$H$9,1,0),"O")
I12	I12	=IFERROR(VLOOKUP($B$5:$B$9,$H$5:$H$9,1,0),"O")
I13	I13	=IFERROR(VLOOKUP($C$5:$C$9,$H$5:$H$9,1,0),"O")
I14:I18	I14	=IFERROR(VLOOKUP($D$5:$D$9,$H$5:$H$9,1,0),"O")
K11	K11	=IFERROR(VLOOKUP($A$5:$A$9,$J$5:$J$9,1,0),"O")
K12	K12	=IFERROR(VLOOKUP($B$5:$B$9,$J$5:$J$9,1,0),"O")
K13	K13	=IFERROR(VLOOKUP($C$5:$C$9,$J$5:$J$9,1,0),"O")
K14:K18	K14	=IFERROR(VLOOKUP($D$5:$D$9,$J$5:$J$9,1,0),"O")
M11	M11	=IFERROR(VLOOKUP($A$5:$A$9,$L$5:$L$9,1,0),"O")
M12	M12	=IFERROR(VLOOKUP($B$5:$B$9,$L$5:$L$9,1,0),"O")
M13	M13	=IFERROR(VLOOKUP($C$5:$C$9,$L$5:$L$9,1,0),"O")
M14:M18	M14	=IFERROR(VLOOKUP($D$5:$D$9,$L$5:$L$9,1,0),"O")
Dynamic array formulas.

 

[Doggson2008]

test.xlsx
	A	B	C	D
3
4	Compare 1	Compare 2
5	Thor	Freya
6	Freya	Atlas
7	Sigrid	Uglük
8	Apollo	Eden
9	Odhen	Apollo
10
11	=OM(LETAUPP(A5:A9;$B$5:$B$9;1)=0;"O";"X")
12	#SAKNAS!
13	X
14	#SAKNAS!
15	#SAKNAS!
16	#SAKNAS!
Blad1

Cell Formulas
Range	Formula
A12:A16	A12	=IF(LOOKUP(A5:A9,$B$5:$B$9,1)=0,"O","X")
Dynamic array formulas.

I don't understand whats wrong with this either Is it really that complicated or am I just very very stupid?

 

[Fluff]

Did you try what I suggested as it works for me

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2
3
4	Team Red	Team Blue	Team Green	Team Purple		Mix&Match A		Mix&Match B		Mix&Match C		Mix&Match D
5	Thor	Atlas	Reyal	Barney		Thor		Freya
6	Freya	Nova	Tommen	Uglük		Atlas		Lewi
7	Sigrid	Signe	Thorend	Loke		Uglük		Kole
8	Apollo	Lewi	Maximo	Sigmund		Eden		Thorend
9	Odhen	Eden	Stig	Vanheli		Apollo		Vanheli
10
11						Red?	TRUE	Red?		Red?		Red?
12						Blue?	TRUE	Blue?		Blue?		Blue?
13						Green?	FALSE	Green?		Green?		Green?
14						Purple?	TRUE	Purple?		Purple?		Purple?
Master

Cell Formulas
Range	Formula
G11	G11	=SUM(COUNTIFS($A$5:$A$9,$F$5:$F$9))>0
G12	G12	=SUM(COUNTIFS($B$5:$B$9,$F$5:$F$9))>0
G13	G13	=SUM(COUNTIFS($C$5:$C$9,$F$5:$F$9))>0
G14	G14	=SUM(COUNTIFS($D$5:$D$9,$F$5:$F$9))>0

 

[Doggson2008]

Yes i did, But now i'm baffled. What am i doing wrong here?
Looking at your example i get very frustrated - Yours is working, Mine is not.
When i copy and paste your exact formula i get this error message.

 

[Fluff]

You need to change the commas to semi-colons.