Compare All cells in Range 1 to All cells in Range 2 – Give result

[MaxTrax]

Hi Guys,

I need two (2) formulas for the following please:

Compare ALL of the values in 1 range to ALL the values in the other range and give result if ANY cells match or don’t match.
(more than 1 cell may match and that’s ok)

I don’t want to compare just 1 cell (eg B1) in range B1:B200 to any cell in range A1:A500

The values will be Integers for one formula and text for the other.
Something like this:
If(Match(Any cell in B1:B200(or defined name) to Any cell in A1:A500), then give “Match” if matched otherwise give “No Match” if not matched.

These formulas will also be used with a *defined name* instead of a range sometimes. I assume ‘Name’ will just replace ‘Range’ in formulas.

They will be used separately throughout the workbook to compare *different* ranges.

Thanks

MaxTrax

 

[Nishant94]

If the side by side cell value have to match like A2=B2, A3=B3, A4=B4, etc. then, this is what you need:

Excel 2016
	A	B	C
1	Values of A	Values of B
2	416	416
3	519	519
4	403	403
5	Data	Data
6	392	392
7	495	495
8	Data	Data
9	335	335
10	446	446
11	329	329
12	543	543
13	396	396
14	489	489
15	187	187
16	499	499
17	545	545
18	596	596
19	460	460
20	319	319
21	350	350
22	125	125
23	459	459
24	575	575
25	313	313
26	481	481
27	492	492
28	519	519
29	500	500
30	Solo	Solo	All Match
31
Sheet1

Cell Formulas
Range	Formula
C30	=IF(SUMPRODUCT(--($A$2:$A$30=$B$2:$B$30))=ROWS($A$2:$A$30),"All Match","")

Otherwise if the matched value can be anywhere in the other column, then:

Excel 2016
	A	B	C
1	Values of A	Values of B
2	416	416
3	519	519
4	403	403
5	Data	Data
6	392	392
7	495	495
8	Data	Data
9	335	335
10	446	446
11	329	329
12	543	543
13	396	396
14	489	489
15	187	187
16	499	499
17	545	545
18	596	596
19	460	460
20	319	319
21	350	350
22	350	460
23	459	459
24	575	575
25	313	313
26	481	481
27	492	492
28	519	519
29	500	500
30	Solo	Solo	All Match
31
Sheet1

Cell Formulas
Range	Formula
C30	=IF(SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$30,$B$2:$B$30,0)))=ROWS($A$2:$A$30),"All Match","")

 

[MaxTrax]

Thank you Sir.....Much appreciated

MaxTrax

 

[MaxTrax]

But what if ranges are different sizes?

 

[Nishant94]

It will still work.

Example:

Excel 2016
	A	B	C
1	Values of A	Values of B
2	416	416
3	519	519
4	403	403
5	Data	Data
6	392	392
7	495	495
8	Data	Data
9	335	335
10	446	446
11	329	329
12	543	543
13	396	396
14	489	489
15	187	187
16	499	499
17	545	545
18	596	596
19	460	460
20	319	319
21	350	350
22	350	460
23	459	459
24	575	575
25	313	313
26	481	481
27	492	492
28	519	519
29	500	500
30	Solo	Solo	All Match
31	492
32	492
33	519
34	500
35	Solo
Sheet1

Cell Formulas
Range	Formula
C30	=IF(SUMPRODUCT(--ISNUMBER(MATCH($A$2:$A$35,$B$2:$B$30,0)))=ROWS($A$2:$A$35),"All Match","")

 

[MaxTrax]

What am I missing?...Can't get this to work.

	A	B	C
1	1	10
2	2	7
3	3	16
4	4	8
5	5	22
6	6	19
7	7	7
8	8	3
9	9	10
10	10
11	11
12	12
13	13
14	14
15	9999	<<<<
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23		All Match

<tbody>
</tbody>

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]IF(SUMPRODUCT(--ISNUMBER(MATCH(B1:B9,A1:A23,0)))=ROWS(B1:B9),"All  Match","-")[/TD]
[/TR]
</tbody>[/TABLE]

Result at C23 ( Note A15)

 

[Nishant94]

Always the lookup value of match and the reference in the row will be the column in which most no. of values are there.

Excel 2016
	A	B	C
1	1	10
2	2	7
3	3	16
4	4	8
5	5	22
6	6	19
7	7	7
8	8	3
9	9	10
10	10
11	11
12	12
13	13
14	14
15	9999	<<<<
16	16
17	17
18	18
19	19
20	20
21	21
22	22
23	23		-
24
Sheet5

Cell Formulas
Range	Formula
C23	=IF(SUMPRODUCT(--ISNUMBER(MATCH(A1:A23,B1:B9,0)))=ROWS(A1:A23),"All Match","-")

 

[MaxTrax]

Ok, I now understand.....Thanks again

MaxTrax

 

[MaxTrax]

Still missing something...........

	A	B	C	D
1	10	20
2	20	50
3	30	60
4	40	30
5	50	120
6	60	140
7	70	120
8	80	20
9	90
10	100
11	110
12	120
13	130
14	140
15	150
16	160
17	170
18	180
19	190
20	200
21	210
22	220
23	230		-

<tbody>
</tbody>

=IF(SUMPRODUCT(--ISNUMBER(MATCH(A1:A23,B1:B8,0)))=ROWS(A1:A23),"All Match","-")

I would think result should be "All Match" at C23

Thanks

 

[MaxTrax]

This works....I think

	A	B	C
1	10	20
2	20	50
3	30	60
4	40	30
5	50	120
6	60	140
7	70	120
8	80	20
9	90
10	100
11	110
12	120
13	130
14	140
15	150
16	160
17	170
18	180
19	190
20	200
21	210
22	220
23	230		All Match

<tbody>
</tbody>

{IF(SUM(--(B1:B8=TRANSPOSE(A1:A23)))=COUNTA(B1:B8),"All Match","-")}

as array at C23

and

	A	B	C
1	10	20
2	20	50
3	30	60
4	40	5555	<<<<<
5	50	120
6	60	140
7	70	120
8	80	20
9	90
10	100
11	110
12	120
13	130
14	140
15	150
16	160
17	170
18	180
19	190
20	200
21	210
22	220
23	230		-

<tbody>
</tbody>

No match at C23 "-"


Thanks

MaxTrax

 

[Peter_SSs]

For your first question, I think you could also use this standard-entry formula, though it does require Excel 2010 or later.
2 examples shown.

Excel Workbook
	A	B	C
1	Values of A	Values of B
2	384	416
3	68	519
4	264	403
5	139	Data
6	453	392
7	190	495
8	295	Data
9	Data	335
10	176	446	Match
11
12
13	Values of A	Values of B
14	384	416
15	68	519
16	264	403
17	139	Data
18	453	392
19	190	495
20	295	Data
21	Item	335
22	176	446	No Match
Match 1

Your second question is unclear to me. You stated:

Match ALL cells in A2:A30 to ALL cells in B2:B30 (or a defined 'Name') and give result "All Match" or "" in C30

In other words *every* cell must match.

Both sets of data in posts #15 and #18 contain cells in column A that do not match anything in column B, yet you seem to be saying that one result should be "All natch" (#18) and one result should be "No Match" (#15).

If you gave smaller samples (say max of 10 rows) and the expected result with clear explanation of why that result, you would probably get your problem solved more quickly.