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]

This will work for both text and number:

Excel 2016
	A	B	C	D
1	384	416		Values in B that are in A
2	68	519		Data
3	264	403		489
4	139	Data		187
5	453	392		Solo
6	190	495
7	295	496
8	Data	335
9	176	446
10	440	329
11	452	543
12	284	396
13	485	489
14	197	187
15	31	499
16	Solo	545
17	169	596
18	187	460
19	473	319
20	253	462
21	408	362
22	255	459
23	489	575
24	317	313
25	20	481
26	18	492
27	310	519
28	272	500
29	412	Solo
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(INDEX($B$1:$B$29,SMALL(IF(ISNA(MATCH($B$1:$B$29,$A$1:$A$29,0)),999999,ROW($B$1:$B$29)-ROW($B$1)+1),ROWS(D$2:D2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Adjust the range according to your nees.

 

[MaxTrax]

An example below:

	A	B	C	D
1	323	111
2	112	359
3	108	5
4	69	123
5	237	277
6	188	32
7	44	114
8	96	218
9	158	69	< Match (69)
10	132	343
11	113	341
12	302	341	Duplicate ok(341)
13	82	8
14	148	170
15	241	47
16	18	69	RESULT HERE

<tbody>
</tbody>

Compare ALL of B1:B16 to ALL of A1:A16. In this case there is a match of 69

Column A1:A16 could be a defined name stored elsewhere.

Thanks

MaxTrax

 

[Nishant94]

Excel 2016
	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
11	440	329
12	452	543
13	284	396
14	485	489
15	197	187
16	31	499
17	Solo	545
18	169	596
19	187	460
20	473	319
21	253	462
22	408	362
23	255	459
24	489	575
25	317	313
26	20	481
27	18	492
28	310	519
29	272	500
30	412	Solo
Sheet1

Cell Formulas
Range	Formula
C2	=IF(COUNTIF(B$2:B2,B2)=1,IF(ISNA(MATCH(B2,$A$2:$A$30,0)),"","),"")

 

[MaxTrax]

Thanks Nishant94...........But I just need a result that says "Match" or "No Match" in C16 (My example)

I don't need to see the actual values displayed. BTW Can you copy array formulas?

Can you also show the formula using a defined name for one range?

I can also use the formula you have given for another use



MaxTrax

 

[Nishant94]

Excel 2016
	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
11	440	329
12	452	543
13	284	396
14	485	489
15	197	187
16	31	499
17	Solo	545
18	169	596
19	187	460
20	473	319
21	253	462
22	408	362
23	255	459
24	489	575
25	317	313
26	20	481
27	18	492
28	310	519
29	272	500
30	412	Solo	Match
Sheet1

Cell Formulas
Range	Formula
C30	{=IF(SUMPRODUCT(--($A$2:$A$30=TRANSPOSE($B$2:$B$30))),"Match","No Match")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MaxTrax]

Thank you Nishant94 but see below….

Can I use this with a ‘Defined Name’ range. The particular ‘Name’ I wish to compare to has only 25 cells. I may wish to compare a range of say 800 cells to it. How can this be achieved please?

I have tried to substitute ‘Name’ for A2:A30 in your formula but I'm not getting a true result (some 'match' some 'no match' after recalculate).

Thanks

MaxTrax

 

[MaxTrax]

Sorry ...Sorry

I didn't enter as array (my bad)

All well

 

[MaxTrax]

Can you please also provide a formula for this:

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.

Thanks

MaxTrax

 

[Nishant94]

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	462	462
22	362	362
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","")