 # Check if 2 ranges have same values (set equality problem)

Hello folks,

Time for another homework problem. Assuming you have 2 ranges of values like below, how do you check if both of them have same set of values? You may assume that these ranges are named range1 range2.

### A bonus question…

How do you find the first equal set of source in the range target?

For example, for below scenario, source = 2nd column of target.

So the expected answer is 2 for this question. Please assume the source & target ranges are named as source and target.

Go ahead and share your comments. I am very keen to see what kind of creative & elegant solutions we can come up to solve this problem.

Want more homework or Excel challenges?

Check out our Excel challenges page & homework tag for more problems. Be warned though, serious workouts for your brain ahead. Your mind will have a six pack at the end of it all. Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

Thank you and see you around.

### Related articles:

 Written by Chandoo Tags: advanced excel, duplicates, homework, Microsoft Excel Formulas, sets, unique items Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

### 71 Responses to “Check if 2 ranges have same values (set equality problem)”

1. Somendra Misra says:

Hi Chandoo,

May be something like:

For first problem:
=IFERROR(SUMPRODUCT(--(MATCH(Range1,Range2,0)>0))=COUNTA(Range1),FALSE)

For Bonus Question, WITH CSE:
=MATCH(TRUE,MMULT(COUNTIF(Source,TRANSPOSE(Target)),{1;1;1;1;1})=COUNTA(Source),0)

2. Desk Lamp says:

Problem1: =AND(range1=range2)
Ctrl+Shift+Enter

• Rob says:

This is great but only works if the items in each range are in the same order. Ranges 1 & 2 above are identical but in different orders

3. Satish says:

For First Problem It May be Just Compare 2 Columns
=Range1=Range2 --- Result will be "True" if column Data match
---- Result will be "False" if Column Data Don't Match

Second Question i not completely understand. what to do in That

4. Elchin Khalilov says:

First of all, I would put an additional column using VLOOKUP function to find whether a value in Range 1 exists in Range 2. And if so I will bring that value using the same VLOOKUP function.
Then using IF function will show me whether value in Range 1 corresponds to the value generated by VLOOKUP function.

5. Dick Byrne says:

Main question:

=AND(NOT(ISNA(MATCH(Range1, Range2, 0))))

Enter as an array formula (CSE)

6. Dick Byrne says:

Ah but what if the ranges are different sizes? Then you need a two way check ...

=AND(NOT(ISNA(MATCH(Range2, Range1, 0))), NOT(ISNA(MATCH(Range1, Range2, 0))))

Entered as an array function (CSE)

7. Jason Morin says:

Assuming ranges are same dimensions as in the example:

=SUMPRODUCT(1*ISNA(MATCH(range1,range2,0)))=0

8. Michael says:

First problem (Array formula):

=AND(COUNTIF(range1,range2)=IF(range1=range1,1,0),COUNTIF(range2,range1)=IF(range2=range2,1,0))

9. Ericson says:

Problem #2

=(Geomean(source)^var.s(source))-(geomean(target2)^var.s(target2))

A zero indicates same. A variety of math equations can be used this is just one. I could have chosen logs. The point is to make the range a unique value and compare the next range using same methodology. Only works for numbers and the probability of this failing falls well below an "I trust excel enough for the array to work"

10. XOR LX says:

Hi Chandoo.

In all of your examples each of the values from a given range is unique within that range. Is it safe to assume that this will always be the case?

Or could you have a case such as:

range1: "DEF", "DEF", "GHI", "JKL", "MNO"
range2: "JKL", "GHI", "MNO", "DEF", "DEF"

?

Regards

• Chandoo says:

Hi XOR LX... you can assume that all values will be unique and ranges will have same size (number of cells).

11. Miguel says:

I know is a easier way to do it and some one is going to clean this bit of code
Thanks a lot Chandoo
========================================
Public Sub sameRange()
Dim setOne As Range
Dim setTwo As Range
Set setOne = Sheets("Sheet1").Range("range1")
Set setTwo = Sheets("Sheet1").Range("range2")

'REMOVE THE COLOR FILL
setOne.Interior.ColorIndex = xlNone

For Each cellitem In setOne
For Each cellItem2 In setTwo
If cellitem = cellItem2 Then
MsgBox "Found match at cell.." & cellitem.Address
Range("E2").Select

ActiveCell.Value = cellitem
cellitem.Interior.ColorIndex = 50
End If
Next cellItem2
Next cellitem
End Sub
======================
🙂

• Zak Naz says:

Great!
How to apply for non-equal contents for every named range in two workbooks with identical named ranges but different ranges?

• Zak Naz says:

Great!
How to use to highlight differences of cell contents of named ranges in two workbooks with identical named ranges and different range areas?

12. Maribeth EM says:

Use function EXACT. It compares two text strings and returns True if exactly the same and False if not. This function however is case-sensitive so if one is capitalized and the other is not, then it will return False.

=EXACT(A1,A2)

assumption is that the text are in cells A1 and A2

13. Linda Campbell says:

Problem 1:

=SUM((COUNTIFS(D3:D7,C3:C7))*1)=COUNTA(C3:C7)
CTL ALT DLT

Returns true if exact match, false otherwise

• Nelson says:

I think you mean Ctrl+Shift+Enter...

• Linda Campbell says:

🙂 I think I do

14. Steve S. says:

I would use conditional formatting for each range with the following formula in the conditional formatting :

For Range 1:

=NOT(IFERROR(VLOOKUP(A2,Range_2,1,0)=A2,0))

For Range 2:

=NOT(IFERROR(VLOOKUP(C2,Range_1,1,0)=C2,0))

This gives me true/false for each cell and highlights any cells that are different.

15. Ysahme says:

Hi Guys...
Good day!!!
Problem #1
I use IF Statement to another column and it return OK if same and blank if it is not...

I also try match but it return me a Number meaning that range2 is in the number base on range1...

16. Harry S says:

' got a bit if VBA instead
'use the data as at least 1 space around to use CurrentRegion

TRUE Range 1 Range 2 Source Target

ABC ABC 123 128 127 123 134 127 140
DEF GHI 124 129 123 126 135 123 141
GHI MNO 125 130 124 125 136 124 142
Not In Col 1 JKL JKL 126 131 126 124 137 125 143
In Col 2 MNO DEF 127 132 125 127 138 132 144
In Col 3 126
Not In Col 4
In Col 5
Not In Col 6

'
Then code as

[code]
Option Explicit
Function AllRaInRB(Ra As Range, Rb As Range) As Boolean
Dim CE As Range
For Each CE In Ra
If Rb.Find(CE) Is Nothing Then GoTo NotThere
' see the Find ( Options .. ) like below if needed
' If Rb.Find(CE, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) Is Nothing Then GoTo NotThere
Next CE
AllRaInRB = True ' found them all
NotThere:
End Function
Private Sub CommandButton21_Click()
Dim Ra As Range, Rb As Range, Rc%, Rot%
'
' assuming Range header one space above the data and the range data is spaced from other data
' the headers for the ranges can be whatever and anywhere in the active sheet
'
With ActiveSheet.UsedRange

Set Ra = .Find("Range 1")(3, 1).CurrentRegion
Set Rb = .Find("Range 2")(3, 1).CurrentRegion

[b2] = AllRaInRB(Ra, Rb) ' part A

Set Ra = .Find("Source")(3, 1).CurrentRegion
Set Rb = .Find("Target")(3, 1).CurrentRegion
Rot = 6 ' row Out start
End With

For Rc = 1 To Rb.Columns.Count ' finding all columns in Target
If AllRaInRB(Ra, Rb.Columns(Rc)) Then
Cells(Rot + Rc, 1) = " In Col " & Rc ' results down column A starting at Rot +1
Else
Cells(Rot + Rc, 1) = "Not In Col " & Rc
End If
Next Rc
End Sub
[/code]

17. Krishna says:

Hi all,

For first question:
{=(ISNUMBER(MATCH(Range1,Range2,0)))}
or as mentioned by Maribeth, we can use Exact function
{=EXACT(Range1, range2)}

Use of CSE as we are dealing with ranges

18. VISHAL says:

For First Problem
=IF(A2=B2,TRUE,FALSE)

19. XOR LX says:

Since Chandoo confirmed that the values in each range are unique within that range, CSE:

=AND(COUNTIF(range1,range2))

Bonus Question, non-array:

=MATCH(5,MMULT({1,1,1,1,1},COUNTIF(source,target)),0)

if the number of rows is fixed at 5, or, CSE:

=MATCH(COUNTA(source),MMULT(TRANSPOSE(ROW(source))^0,COUNTIF(source,target)),0)

if the number of rows is dynamic.

Regards

• Oscar says:

XOR LX

Congratulations, your two first formulas are exactly the same formulas I was about to post here but you were faster.

• Somendra Misra says:

XOR,

Clever way of changing array1 with array2. 🙂

See my formula in comment #1.

Regards,

• XOR LX says:

@Somendra

Yes - yours was of course the first post I saw, and a very good one as well.

I just saw the opportunity to tweak your MMULT construction so that we donn't require the TRANSPOSE. 🙂

Cheers

20. Gabriel says:

=AND(SUM(SUM(--(vector_2=vector_1)))=COUNTA(vector_1),COUNTA(vector_1)=COUNTA(vector_2))
Control+Shift + Enter. ( Array Formula )
example:( for ture)
vector 2 vector 1
abc abc
dei dei
copy copy
try try
bug bug
loop loop
example for False:
vector 2 vector 1
abc abc
bei dei
sopi copy
try try
bug bug
loop loop

21. XOR LX says:

@Oscar

Cheers! And sorry! 🙂

• Oscar says:

@XOR LX

I wouldn´t have made the bonus question formula without learning the MMULT function from your blog.

22. Ilyas says:

For problem 1 i tried the following formula:
{=ISNUMBER(SUM(MATCH(Range2,Range1,0)))}

For problem 2 i used the following formula in conditional formatting so that the matching column gets highlighted:

=ISNUMBER(SUM(MATCH(source,OFFSET(target,1,C\$9-1,5,1),0)))

23. Bhavesh Soni says:

Sum(match(range1)=(match(range2) will show true if all items are there. Note enter this as Array formula.

24. Bhavesh Soni says:

Sum(match(range1)=(match(range2) will show true if all items are there. Note enter this as Array formula

25. cllach says:

For first one:

=NOT(ISERROR(PRODUCT(MATCH(IF(Range1="""",""""&COUNTBLANK(Range1),Range1 & COUNTIF(Range1,Range1)),IF(Range2="""",""""&COUNTBLANK(Range2),Range2& COUNTIF(Range2,Range2)),0))))

I think that works returning True for vertical ranges even if values are not unique (must appear same number of times), or empty or numeric, if ranges are diferent size it returns False.

Easy to adapt to second one.

• cllach says:

Sorry, pasting doubled quotes...
=NOT(ISERROR(PRODUCT(MATCH(IF(Range1="",""&COUNTBLANK(Range1),Range1 & COUNTIF(Range1,Range1)),IF(Range2="",""&COUNTBLANK(Range2),Range2& COUNTIF(Range2,Range2)),0))))
============================================

• cllach says:

Think that works for unique or not, values, returns True if found same number of times, numeric or text and return False if ranges are diferent size. Easy to adapt to second problem.

26. Lokesh says:

For the main question.

=VLOOKUP([@[Range 2]],Table1[Range 1],1,0)=[@[Range 2]]

If all values are true than the values in both range match

27. Vad says:

=IFERROR(SUM(MATCH(C3:C7,E3:E7,0)),"No Match")
Array Formula

I am expecting it work irrespective of number of items in both the lists,,

28. Nelson says:

"=IF(NOT(ISERROR(VLOOKUP(C1,Range2,1,0))),"True","False")"

29. Fern says:

Highlight the 2 ranges
Under Conditional Formatting, Select "Highlight Cell Rules", select "Duplicate Values"
The values that match in each range will be highlighted.

You can also choose to highlight only Unique value in each range

30. CraigM says:

use {=OR(Exact(A1,range2))}

where:
the cells in range1 are A1 to A5
the cells in range2 are B1 to B5

Enter the formula in cell C1. This will check to see if the value in A1 exists in range2. Copy down for the rest of the values.

To check if each of the values in range1 exist in range2, use:
{=OR(Exact(B1,range1))} in D1 & then copy down for the rest.

This is useful for lists that aren't too large. If they are, then resources/performance becomes an issue.

31. Dev says:

Use Conditional Formatting and use formula:
where A1 is the first cell in Range 1 i.e. ABC

So, if ABC is not listed in Range 2, ABC will be highlighted in Blue

32. Bhavesh Soni says:

=SUM(MATCH(RANGE1,RANGE1,0))=(SUM(MATCH(RANGE2,RANGE2,0)))
Enter this as array formula.

33. shanmugam says:

Assumtion Range1 A1 to A6, Range 2 B1 to B6

=Vlookup(A2,\$A\$2:\$A\$6,2,0)

34. Shaji says:

first one is so simple...
{=if(COUNTIF(Range1,Range2)=1,"True","false")}

2nd one to find out...

• Shaji says:

Sorry,
this one is correct for the first question...
{=SUM(COUNTIF(Range2,Range1))=ROWS(Range1)}

35. Aravind says:

Hai,Good Morning

In the first one even though the values are same but the cells are different know so,I will make the criteria with cell numbers and find the answer

36. Ian says:

=IF(IFERROR(MATCH(A1,B:B,0),0)=0,"No Match","Matched")

37. Anand says:

I simply use conditional formatting feature.

Select both range and use conditional formatting to see duplicate/unique values.

38. Belle-Isle says:

For first problem :

CSE : =AND(COUNT(Range1)=COUNT(Range2), COUNTIF(Range2, Range1)=1, COUNTIF(Range1, Range2)=1)

This returns true in the wanted situation (identical arrays of unique values, whatever their order), but returns false if the arrays are of different sizes or if there are any duplicates in any one array. All in a single formula.

39. Abhijeet says:

My source data: A3:A7
Target Ranges: C3:F7
Select Target Range and use conditional formatting formula "=COUNTIF(\$A\$3:\$A\$7,C3)" with some colour to highlight cells matching criteria. If all cells in a column are colored, it's a match target range.

40. DJP says:

Good morning / Good afternoon,

May I suggest this formula:
SUM(--(Range1=TRANSPOSE(Range2)))=COUNTA(Range1)
As an array formula, Ctrl+Shift+Enter is required

For Frenchies, it gives:
SOMME(--(Range1=TRANSPOSE(Range2)))=NBVAL(Range1)
Ctrl+Shift+Enter needed too 😉

Cheers

41. sam says:

Problem 1
=AND(COUNTIF(Rng1,Rng2))
Array Entered
True if both are the same, False if they are different

42. Jorge Eduardo (brazil) says:

=contREP(O3:S7;H3:L17)
vai informar a quantidade de valores repetidos entre 2 range

funciona para mim e uso muito dentro de macros

Dim reg1() As Variant, reg2() As Variant, ttl As Long
Dim lc1 As Long, cc1 As Long, lc2 As Long, cc2 As Long

Function ContREP(ByVal rag1 As Range, ByVal rag2 As Range) As Long

ttl = 0
reg1 = rag1.Value2
reg2 = rag2.Value2
lc1 = UBound(reg1, 1)
cc1 = UBound(reg1, 2)
lc2 = UBound(reg2, 1)
cc2 = UBound(reg2, 2)

For l1 = 1 To lc1
For c1 = 1 To cc1
If reg1(l1, c1) "" Then GoSub lk
Next
Next
ContREP = ttl
Exit Function
lk:
For l2 = 1 To lc2
For c2 = 1 To cc2
If reg1(l1, c1) = reg2(l2, c2) Then ttl = ttl + 1: Return
Next
Next
Return

End Function

• Jorge Eduardo (brazil) says:

https://www.sendspace.com/file/fsr0k5

43. Abbott Katz says:

Assuming the two ranges are in B8:B12 and C8:C12 respectively:

{=IF(ISNA(SUM(MATCH(B8:B12,C8:C12,0))),"Incomplete match","Complete match")}

44. QL says:

My approach to the first question:

Assume range_1 and range_2 are arranged as vertical column range with same size, and there might be duplicate values in the range.

The array formula
=SUM(--(MMULT(--(TRANSPOSE(Range_1)=Range_1),--(ROW(Range_1)=ROW(Range_1)))=MMULT(--(TRANSPOSE(Range_1)=Range_2),--(ROW(Range_1)=ROW(Range_1))))) = ROWS(Range_1)

45. Katie Grimes says:

Assuming the two ranges are in A2:A6 and B2:B6, I created a row beneath the two ranges to tell me how many values are MISSING from Range 1 in Range 2 and vice versa. If they are a perfect match, there would be 0 missing values in each column. (I decided to do missing values instead of matching values so that I wouldn't have to know HOW MANY values were in the range to begin with.)

My array formula for A7 = SUM(IF(ISNA(MATCH(A2:A6, B2:B6, 0)), 1, 0)), Ctrl + Shift + Enter. This would give 0 if every value in Range 1 could be found in Range 2.

Similarly, for B7 = SUM(IF(ISNA(MATCH(B2:B6, A2:A6, 0)), 1, 0)). If both A7 and B7 = 0, then you have a perfect match.

In addition, I wanted to be able to tell which values WERE missing if they did not match perfectly. So I created a conditional formatting in cells A2:A6 using the formula = ISNA(MATCH(A2, \$B\$2:\$B\$6,0)). This would highlight any cells in Range 1 that could not be found in Range 2. I then repeated the formatting for B2:B6 using the formula condition = ISNA(MATCH(B2, \$A\$2:\$A\$6, 0)).

For question 2:

Assume the Source Range is in K3:K7, and the Target Table has headers in M2:R2, and the data is in M3:R7.

Beneath each of the ranges in the Target table, I inserted a row with the same array formula used above to compare it to the Source Range:
M8 = SUM(IF(ISNA(MATCH(M3:M7, \$K\$3:\$K\$7, 0)), 1, 0)), Ctrl + Shift + Enter.
N8 = SUM(IF(ISNA(MATCH(N3:N7, \$K\$3:\$K\$7, 0)), 1, 0)), Ctrl + Shift + Enter.
O8 = SUM(IF(ISNA(MATCH(O3:O7, \$K\$3:\$K\$7, 0)), 1, 0)), Ctrl + Shift + Enter.
P8 = SUM(IF(ISNA(MATCH(P3:P7, \$K\$3:\$K\$7, 0)), 1, 0)), Ctrl + Shift + Enter.
Q8 = SUM(IF(ISNA(MATCH(Q3:Q7, \$K\$3:\$K\$7, 0)), 1, 0)), Ctrl + Shift + Enter.
R8 = SUM(IF(ISNA(MATCH(R3:R7, \$K\$3:\$K\$7, 0)), 1, 0)), Ctrl + Shift + Enter.

Then, in cell K10 (where I wanted to pull the Range that matched, i.e. where I wanted my answer to appear), I used a Match formula to find the range with 0 missing values.

K10 = MATCH(0, M8:R8, 0). This returned 2, our given range.

I also decided that I wanted to be able to return the Range Name, even if it wasn't 1-6. Changing Range 2 to be called "Apple" (no reason, just the word I picked), in cell K11, I used an Index-Match formula.

K11 = INDEX(M2:R2, ,MATCH(0,M8:R8,0)) This then returned "Apple" instead of "2".

The only thing I realize now as I am typing this is that I didn't do a back check: I didn't check that the Source range had all the values in the Target Range 2. Given they had the same number of values, this isn't a big deal, but ideally, I would have added that as well.

46. David says:

Problem 1:
{=AND(COUNTIF(RANGE1,RANGE2)*COUNTIF(RANGE2,RANGE1))}

47. Alex Groberman says:

If there can be duplicate values and/or a different number of rows in each set:

{=IFERROR(AND(SMALL(MATCH(range1,range2,0),ROW(INDIRECT("1:"&ROWS(range1))))=ROW(INDIRECT("1:"&ROWS(range2)))),FALSE)}

48. Ramakrishnan C says:

Hi sir,
for Q1
two option I tried correct me if I am wrong
a) conditional formatting- cell value(range 1) (giving colour) stop if true
b) match function - by which range1 order is matched with range 2
showing a result like
Rang1 Range2 Order
ABC ABC 1
DEF GHI 3
GHI MNO 5
JKL JKL 4
MNO DEF 2

49. Neil says:

For question two here is my starter for ten that has not been tidied up but should provide some food for thought:

CSE enter:
{=MATCH(MAX(MMULT(COUNTIF(CHK_RNG,TRANSPOSE(CHOOSE(COLUMN(A1:F1),TEST1,TEST2,TEST3,TEST4,TEST5,TEST6))),ROW(A1:A5)^0)),
MMULT(COUNTIF(CHK_RNG,TRANSPOSE(CHOOSE(COLUMN(A1:F1),TEST1,TEST2,TEST3,TEST4,TEST5,TEST6))),ROW(A1:A5)^0),0)}

I set up a range called CHK_RNG in cells B4:B8 for the data to be checked against. And six ranges for the guesses called TEST1 (D4:D8), TEST2 (E4:E8), ...TEST6 (I4:I8)

50. Micah Dail says:

First problem is pretty straighforward.

{=AND(ISNUMBER(MATCH(range1, range2, 0)))}

Second one is rather tricky. Haven't figured it out yet.

51. parveen says:

Hello Chandoo,
As per me exact function should be used.

52. Ale says:

My solution to the first question:

=AND(MATCH(INDEX(Range1,1),Range2,0),MATCH(INDEX(Range1,2),Range2,0),MATCH(INDEX(Range1,3),Range2,0),MATCH(INDEX(Range1,4),Range2,0),MATCH(INDEX(Range1,5),Range2,0))

53. Ale says:

And this is my solution for the bonus question:

IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target1,0),MATCH(INDEX(Source,2),Target1,0),MATCH(INDEX(Source,3),Target1,0),MATCH(INDEX(Source,4),Target1,0),MATCH(INDEX(Source,5),Target1,0)))),D15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target2,0),MATCH(INDEX(Source,2),Target2,0),MATCH(INDEX(Source,3),Target2,0),MATCH(INDEX(Source,4),Target2,0),MATCH(INDEX(Source,5),Target2,0)))),E15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target3,0),MATCH(INDEX(Source,2),Target3,0),MATCH(INDEX(Source,3),Target3,0),MATCH(INDEX(Source,4),Target3,0),MATCH(INDEX(Source,5),Target3,0)))),F15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target4,0),MATCH(INDEX(Source,2),Target4,0),MATCH(INDEX(Source,3),Target4,0),MATCH(INDEX(Source,4),Target4,0),MATCH(INDEX(Source,5),Target4,0)))),G15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target5,0),MATCH(INDEX(Source,2),Target5,0),MATCH(INDEX(Source,3),Target5,0),MATCH(INDEX(Source,4),Target5,0),MATCH(INDEX(Source,5),Target5,0)))),H15,IF(NOT(ISERROR(AND(MATCH(INDEX(Source,1),Target6,0),MATCH(INDEX(Source,2),Target6,0),MATCH(INDEX(Source,3),Target6,0),MATCH(INDEX(Source,4),Target6,0),MATCH(INDEX(Source,5),Target6,0)))),I15,"no match"))))))

Tip: any idea on how to make it shorter?

54. Eric Lind says:

Count if makes the most sense to me.

The basic formula would be:

=COUNTIF(Range2,A2)

which is nice because you can count how many instances of a value in the range.

55. Ryan Wells says:

I know you can do this with formulae, but I'm partial to VBA. Since I have to compare strings regularly for nuclear engineering design verification, I created a VBA code that doesn't stop at simply comparing the ranges. It will tell you where in Range 2 the value in Range 1 appears and it will highlight the cells not found in both ranges. If you're interested: http://wellsr.com/vba/2015/excel/examples/excel-compare-two-columns-for-differences/

56. Ramesh Deo says:

(=OR(range1=range2)}

57. Ramesh Deo says:

{=OR(range1=range2)}

 « Celebrate Holi with this colorful Excel file Share your favorite Excel tip & you could win Beats Headphones [Podcast Anniversary Celebrations] »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.