How to find common values in 3 columns in Excel?
Working with data in Excel often involves comparing lists to identify shared or duplicated entries. While comparing two columns to locate common values is a frequent task, there are situations where you need to determine which values appear in three separate columns at the same time. For example, when consolidating survey data, merging sales records or analyzing duplicate entries across multiple lists, it's important to accurately extract the set of items present in all three columns, just as demonstrated in the screenshot below. This article introduces several practical methods to solve this problem in Excel, allowing you to efficiently and reliably pinpoint the common values among three columns—whether you prefer formulas, VBA.
Find common values in 3 columns with array formulas
VBA macro to extract values present in all three columns
Find common values in 3 columns with array formulas
To find and extract common values among three columns, you can use array formulas designed to search for items that appear in all selected ranges. This is particularly useful in datasets where you don't want to rely on additional Excel add-ins or external tools.
Enter this array formula into a blank cell where you want to display the first common value:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))How to use this array formula:
- After entering the formula into your selected cell, press Shift + Ctrl + Enter (not just Enter). Excel will enclose the formula in curly braces to indicate it's an array formula.
- Drag the formula down the column until blank cells appear. This will list all values shared by the three columns, and blank cells will indicate that no further matches exist.
Notes and parameter explanations:
- If you prefer a different array formula, this one also returns all unique values present in all three columns:
Again, remember to press Shift + Ctrl + Enter after typing or pasting the formula.=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0)) - In these formulas:
- A2:A10, B2:B8, C2:C9 are the data ranges in each of the three columns you wish to compare.
- E1 refers to the cell directly above where your formula starts (for exclusion logic). Adjust cell references to match your actual data ranges and the location where you want results to appear.
- These methods work well for moderate data sets, but may become slow on very large volumes due to the calculation demands of array formulas.
- Take care to avoid adjusting source ranges mid-way, as it can lead to inaccurate results or formula errors.
- If the result includes blank rows, it means that all common values have been extracted and the remaining cells have no further intersections.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
VBA macro to extract values present in all three columns
If you prefer an automated approach that does not require entering or copying complex formulas, you can use Excel VBA to loop through your data and output only those values that are present in each of the three columns. This method is particularly useful for very large data sets or when working with dynamic ranges, as VBA is more efficient at handling repetitive tasks and custom criteria.
1. Click Developer > Visual Basic to open the VBA editor (if the Developer tab is not visible, you can enable it via File > Options > Customize Ribbon).
2. In the VBA editor, click Insert > Module to create a new module. Then paste the code below into the module window:
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub3. In the VBA window, with the module selected, press F5 or click the Run (▶) button to execute the code. You will be prompted, in sequence, to select each of the three column ranges you wish to compare. Use your mouse to highlight the appropriate cells during each prompt.
4. The macro will process your selections and output all values present in all three columns to the next empty column to the right of your current data set, starting from the first row.
This method is efficient when working with large or dynamic data sets and can easily be extended to four or more columns by duplicating the dictionary logic. Always remember to save your workbook before running macros, as unsaved changes cannot be undone if you wish to revert.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in