Find Duplicates with VLOOKUP or MATCH in Excel & Google Sheets
In this Article
This tutorial will demonstrate how to find duplicate values using VLOOKUP and Match in Excel and Google Sheets.
Find Duplicate Values – VLOOKUP Function
To identify duplicate values in 2 columns in a range of cells in Excel you can use the VLOOKUP Function:
|
1 |
=VLOOKUP($B$4:B$14,$C$4:$C$14, TRUE, FALSE) |
If the cities are found in both columns B and C, then they will show in column D.
Find Duplicate Values – MATCH Function
Similarly, you can also find duplicate values in 2 columns using the MATCH Function.
|
1 |
=MATCH(B4,$C$4:$C$14,FALSE) |
The MATCH Function will return the row number if the duplicate exists, and a #N/A if the value is not duplicated.
Return Duplicate Value – the IF Function
If you want to return the duplicate value instead of the row number, you can expand the formula created by the MATCH Function to include an IF Function.
Select D4 and type the following formula.
|
1 |
=IF(MATCH(B4,$C$4:$C$14,FALSE)>0,B4,"") |
This will give us the exact same result as using the VLOOKUP to return the duplicate value.
Find Duplicate Values – VLOOKUP Function in Google Sheets
Finding duplicate values in 2 columns using VLOOKUP works exactly the same in Google sheets as it does in Excel.
|
1 |
<span class=" default-formula-text-color" dir="auto">=</span><span class=" default-formula-text-color" dir="auto">VLOOKUP</span><span class=" default-formula-text-color" dir="auto">(</span><span dir="auto">$A$2:A$12</span><span class=" default-formula-text-color" dir="auto">,</span><span dir="auto">$B$2:$B$12</span><span class=" default-formula-text-color" dir="auto">,</span> <span class="boolean" dir="auto">TRUE</span><span class=" default-formula-text-color" dir="auto">,</span> <span class="boolean" dir="auto">FALSE</span><span class=" default-formula-text-color" dir="auto">)</span> |
Find Duplicate Values in 2 columns – MATCH Function in Google Sheets
The Match function in Google Sheets is also identical to Excel.
|
1 |
<span class=" default-formula-text-color" dir="auto">=</span><span class=" default-formula-text-color" dir="auto">MATCH</span><span class=" default-formula-text-color" dir="auto">(</span><span dir="auto">A2</span><span class=" default-formula-text-color" dir="auto">,</span><span dir="auto">$B$2:$B$12</span><span class=" default-formula-text-color" dir="auto">,</span><span class="boolean" dir="auto">FALSE</span><span class=" default-formula-text-color" dir="auto">)</span> |
You can also use the IF Function to return the duplicate value.
|
1 |
<span class=" default-formula-text-color" dir="auto">=</span><span class=" default-formula-text-color" dir="auto">IF</span><span class=" default-formula-text-color" dir="auto">(</span><span class=" default-formula-text-color" dir="auto">MATCH</span><span class=" default-formula-text-color" dir="auto">(</span><span dir="auto">A2</span><span class=" default-formula-text-color" dir="auto">,</span><span dir="auto">$B$2:$B$12</span><span class=" default-formula-text-color" dir="auto">,</span><span class="boolean" dir="auto">FALSE</span><span class=" default-formula-text-color" dir="auto">)</span><span class=" default-formula-text-color" dir="auto">></span><span class="number" dir="auto">0</span><span class=" default-formula-text-color" dir="auto">,</span><span dir="auto">A2</span><span class=" default-formula-text-color" dir="auto">,</span><span class=" string " dir="auto">""</span><span class=" default-formula-text-color" dir="auto">)</span> |
<<LINK TO ….>> compare-two-columns-duplicates
<<LINK TO ….>> formula-find-duplicate-values
<<LINK TO ….>> check-for-duplicates
Excel Practice Worksheet
Practice Excel functions and formulas with our 100% free practice worksheets!
- Automatically Graded Exercises
- Learn Excel, Inside Excel!