www.automateexcel.com Open in urlscan Pro
2a00:1450:4001:811::2001  Public Scan

Form analysis
3 forms found in the DOM

<form>
  <div class="mx-auto flex flex-col-reverse justify-center gap-2 sm:flex-row"><textarea class="w-full flex-1 resize-none rounded-2xl border bg-gray-100 px-4 py-2 sm:rounded-r-none" rows="2"></textarea><button
      class="self-end rounded-2xl bg-blue-700 px-4 py-2 text-lg font-bold text-white sm:self-stretch sm:rounded-l-none sm:p-6" type="submit">Send</button></div>
</form>

/formula-generator-ai-bot/

<form class="bg-gray-300 p-8 my-8" action="/formula-generator-ai-bot/">
  <h2 class="text-2xl pb-4 text-center">Try our AI Formula Generator</h2>
  <div class="mx-auto flex justify-center">
    <input type="text" class="flex-1 rounded-l-2xl rounded-r-none border bg-gray-100 px-4 py-2 w-full" name="query" value="" placeholder="Ex. Sum all values in Column B where Column A equal Q1" required="">
    <button class="self-end bg-blue-600 text-white font-bold px-4 py-2 self-stretch rounded-l-none rounded-r-2xl" type="submit">Generate</button>
  </div>
</form>

/formula-generator-ai-bot/

<form class="bg-gray-300 p-8 my-8" action="/formula-generator-ai-bot/">
  <h2 class="text-2xl pb-4 text-center">Try our AI Formula Generator</h2>
  <div class="mx-auto flex justify-center">
    <input type="text" class="flex-1 rounded-tl-2xl border bg-gray-100 px-4 py-2 w-full" name="query" value="" placeholder="Ex. Sum all values in Column B where Column A equal Q1" required="">
    <button class="self-end bg-blue-600 text-white font-bold px-4 py-2 self-stretch rounded-tr-2xl" type="submit">Generate</button>
  </div>
  <div class="flex gap-4 rounded-b-3xl border border-gray-300 bg-gray-200 p-4">
    <div class="items-center overflow-hidden rounded-l-xl bg-white hidden sm:flex"><img class="flex items-center" width="157" height="34" loading="lazy" data-cfsrc="https://www.automateexcel.com/formulas-functions/images/formulabar.png"
        src="https://www.automateexcel.com/formulas-functions/images/formulabar.png"></div>
    <div class="flex-grow bg-white px-4 py-2 text-lg text-gray-500 rounded-r-xl">=SUMIF(A:A, "Q1", B:B)</div>
  </div>
</form>

Text Content

 * Start Here
 * VBA
   * VBA Tutorial
     
     Learn the essentials of VBA with this one-of-a-kind interactive tutorial.
     
     
   * VBA Code Generator
     
     Essential VBA Add-in – Generate code from scratch, insert ready-to-use code
     fragments
     
     
   * VBA Code Examples
     
     100+ VBA code examples, including detailed walkthroughs of common VBA
     tasks.
     
     
 * Excel
   * Formulas Tutorial
     
     Learn 30 of Excel’s most-used functions with 60+ interactive exercises and
     many more examples.
     
     
   * Excel Tutorials
     
     List of 600 How To articles for Excel and google Sheets.
     
     
   * Formulas List
     
     Growing list of Excel Formula examples (and detailed descriptions) for
     common Excel tasks.
     
     
   * Excel Boot Camp
     
     Learn Excel in Excel – A complete Excel tutorial based entirely inside an
     Excel spreadsheet.
     
     
 * Shortcuts
   * Shortcut Training App
     
     Interactive shortcut training app – Learn 70+ of Excel’s most useful
     shortcuts.
     
     
   * List of Shortcuts
     
     List of 200+ Excel shortcuts. Bookmark and come back to reference.
     
     
   * Shortcut Coach
     
     Excel shortcut training add-in – Learn shortcuts effortlessly as you work.
     
     
 * charts
   * Chart Templates
     
     Chart Templates (Free Downloads)
     
     
   * Chart Add-in
     
     The ultimate Excel charting Add-in. Easily insert advanced charts.
     
     
   * Charts List
     
     List of all Excel charts.
     
     
 * AI Formula Generator

AutomateExcel
Hi there, I'm AutomateExcel's bot. How can I help you?
Send

Return to Excel Formulas List


FIND DUPLICATES WITH VLOOKUP OR MATCH IN EXCEL & GOOGLE SHEETS

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2023
Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to find duplicate values using VLOOKUP and
Match in Excel and Google Sheets. If your version of Excel allows it, we
recommend using the XLOOKUP Function to handle duplicates instead.




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:

=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.

=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.

 


TRY OUR AI FORMULA GENERATOR

Generate


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.

=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.


ADDITIONAL TUTORIALS ON DUPLICATES

 * Show only duplicates without a formula
 * Clear duplicate cells
 * Filter duplicate values


FIND DUPLICATE VALUES IN GOOGLE SHEETS


VLOOKUP FUNCTION

Finding duplicate values in 2 columns using VLOOKUP works exactly the same in
Google sheets as it does in Excel.

=VLOOKUP($A$2:A$11,$B$2:$B$12, TRUE, FALSE)




FIND DUPLICATE VALUES IN 2 COLUMNS – MATCH FUNCTION IN GOOGLE SHEETS

The Match function in Google Sheets is also identical to Excel.

=MATCH(A2,$B$2:$B$12,FALSE)






You can also use the IF Function to return the duplicate value.

=IF(MATCH(A2,$B$2:$B$12,FALSE)>0,A2,"")




AI FORMULA GENERATOR



Try for Free

AI Tools

 * Excel Help Bot
 * Formula Generator
 * Formula Explainer
 * VBA Code Generator
 * VBA Code Commenter
 * Excel Template Generator


EXCEL PRACTICE WORKSHEET

Practice Excel functions and formulas with our 100% free practice worksheets!

 * Automatically Graded Exercises
 * Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List

← Assign Number Value to Text – Excel & Google Sheets
Find and Extract Number from String – Excel & Google Sheets →



TRY OUR AI FORMULA GENERATOR

Generate
=SUMIF(A:A, "Q1", B:B)

Company

 * About
 * Editorial Team
 * Contact Us
 * Reviews
 * 940 Reviews

AI

 * Formula Generator
 * VBA Generator
 * Table Generator
 * Help Chat

VBA

 * Code Generator
 * Interactive VBA Tutorial
 * 500+ Code Examples

Excel

 * Formulas
 * How-Tos
 * Charts
 * Interactive Formulas Tutorial


 * Disclaimer
 * Privacy Policy
 * Cookie Policy
 * Terms and Conditions
 * Your Privacy Choices
 * Notice at Collection

Copyright © 2024. Microsoft® And Excel® are trademarks of the Microsoft group of
companies. Microsoft does not endorse, promote, or warrant the accuracy of
information found on AutomateExcel.com.


×


NOTICE

We and selected third parties use cookies or similar technologies for technical
purposes and, with your consent, for functionality, experience, measurement and
marketing (personalized ads) as specified in the cookie policy.

With respect to advertising, we and 856 selected third parties, may use precise
geolocation data, and identification through device scanning in order to store
and/or access information on a device and process personal data like your usage
data for the following advertising purposes: personalised advertising and
content, advertising and content measurement, audience research and services
development.

You can freely give, deny, or withdraw your consent at any time by accessing the
preferences panel. If you give consent, it will be valid only in this domain.
Denying consent may make related features unavailable.



Use the “Accept all” button to consent. Use the “Reject all” button to continue
without accepting.

Necessary
Functionality
Experience
Measurement
Marketing
Press again to continue 0/1
Learn more
Reject allAccept all