Compare two arrays to return "Yes/No" result

[hartsie]

I have two arrays that I want to compare. The first array is user-defined and the second array is dynamic. Each array contains a list of numbers. If the second array has cells with values that match the first array, I want to return a "Yes."

For example, Array one has the numbers 1, 3, 9999, each found within one cell in an array of 10 cells. Array two, also made up of ten cells, on a different worksheet, might have one of those three values, or none of them. If Array two does contain one of the numbers found in Array one, I want to return a "Yes."

I feel like I am so close to having an answer.... but I have been spinning and I can't quite get there...

I hope someone knows a good solution.

 

[JamesCanale]

Mr Excel Playground 3.xlsm
	A	B	C	D	E
1	Array 1		Array 2		Array 3
2	1		100		3
3	3		102		200
4	999		104		202
5	4		106		204
6	5		108		206
7	6		110		999
8	7		112		210
9	8		114		212
10	9		116		214
11	10		118		216
12
13	Yes
Sheet2

Cell Formulas
Range	Formula
A13	A13	=IF(SUM(IFERROR(MATCH(Array3,Array1,0),0))>0,"Yes","No")

Named Ranges
Name	Refers To	Cells
Array1	=Sheet2!$A$2:$A$11	A13
Array3	=Sheet2!$E$2:$E$11	A13

 

[hartsie]

@JamesCanale Thank you! I was able to get the function to work by holding Control and Shift before executing the function.

 

[hartsie]

@JamesCanale That is a brilliant solution. I have no idea how you thought of that... I am familiar with each of the functions... however, the nesting is still over my head. That was a great solution. Thank you

 

[JamesCanale]

Array formulas are gold. I never thought much of them a year ago. Hang around on mrexcel for a while and your skill will double.

 

[hartsie]

Array formulas are gold. I never thought much of them a year ago. Hang around on mrexcel for a while and your skill will double.

This is my new favorite website...

 

[Egitman]

Mr Excel Playground 3.xlsm
	A	B	C	D	E
1	Array 1		Array 2		Array 3
2	1		100		3
3	3		102		200
4	999		104		202
5	4		106		204
6	5		108		206
7	6		110		999
8	7		112		210
9	8		114		212
10	9		116		214
11	10		118		216
12
13	Yes
Sheet2

Cell Formulas
Range	Formula
A13	A13	=IF(SUM(IFERROR(MATCH(Array3,Array1,0),0))>0,"Yes","No")

Named Ranges
Name	Refers To	Cells
Array1	=Sheet2!$A$2:$A$11	A13
Array3	=Sheet2!$E$2:$E$11	A13

Hello James, I have a similar issue that I hope you can help resolve. In my case I need to verify if a person is qualified based on a group of questions. This is where I have some conditional formatting indicating if they failed with a yellow highlight. So, in short, if anyone has no highlight then he/she should pass. Can you advise a formula to achieve this please ?

Thank you !