How to compare two columns to find duplicates in Excel

Skip to main content
  • Login
  • Cart

Exceljet

Exceljet

Quick, clean, and to the point
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Blog

Most frequently occurring text

Excel formula: Most frequently occurring text

Generic formula 
= INDEX (rng, MODE ( MATCH (rng,rng,0)))
Explanation 

To extract the word or text value that occurs most frequently in a range, you can use a formula based on several functions INDEX, MATCH, and MODE.

In the example shown, the formula in H5 is:

= INDEX (B5:F5, MODE ( MATCH (B5:F5,B5:F5,0)))

How this formula works

Working from the inside out, the MATCH function matches the range against itself. That is, we give the MATCH function the same range for lookup value and lookup array (B5:F5). 

Because the lookup value contains more than one value (an array), MATCH returns an array of results, where each number represents a position. In the example shown, the array looks like this:

1,2,1,2,2

Wherever “dog” appears, we see 2, and Wherever “cat” appears, we see 1. That’s because the MATCH function always returns the first match, which means subsequent occurrences of a given value will return the same (first) position.

Next, this array is fed into the MODE function. MODE returns the most frequently occurring number, which in this case is 2. The number 2 represents the position at which we’ll find the most frequently occurring value in the range.

Finally, we need to extract the value itself. For this, we use the INDEX function. For array, we use the range of values (B5:F5). The row number is provided by MODE.

INDEX returns the value at position 2, which is “dog”.

Empty cells

To deal with empty cells, you can use the following array formula, which adds an IF statement to test for empty cells:

= INDEX (B5:F5, MODE ( IF (B5:F5<>"", MATCH (B5:F5,B5:F5,0))))

This is an array formula , and must be entered with control + shift + enter.

Author 
Dave Bruns

Related formulas 
Excel formula: Most frequent text with criteria

Most frequent text with criteria
To find the most frequently occurring text in a range, based on criteria you supply, you can use an array formula based on several Excel functions INDEX, MATCH, MODE, and IF. In the example shown, the formula in G5 is: = INDEX ( supplier , MODE ( IF…

Excel formula: Summary count with COUNTIF

Summary count with COUNTIF
When working with data, a common need is to perform summary calculations that show total counts in different ways. For example, total counts by category, color, size, status, etc. The COUNTIF function is a good way to generate these kinds of totals…

Excel formula: Most frequently occurring number

Most frequently occurring number
To find the most frequently occurring number in a range, you can use the MODE function. In the example shown, the formula in M4 is: = MODE ( B4:K4 ) How this formula works The MODE function is fully automatic and will return the most frequently…

Excel formula: List most frequently occuring numbers

List most frequently occuring numbers
To list the most frequently occurring numbers in a column (i.e. most common, second most common, third most common, etc), you can an array formula based on four Excel functions, IF, MODE, MATCH, and ISNUMBER. In the example shown, the formula in D5…

Related functions 
Excel INDEX function

Excel INDEX Function
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to…

Excel MATCH function

Excel MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX…

Excel MODE function

Excel MODE Function

The Excel MODE function returns the most frequently occurring number in a numeric data set. For example, =MODE(1,2,4,4,5,5,5,6) returns 5.

Related videos 
Tip: How to look things up with INDEX

How to look things up with INDEX
In this video, we look at how to configure INDEX to fetch a value by row and column. This is half of the INDEX/MATCH equation.

Tip: How to use the MATCH Function for exact matches

How to use the MATCH Function for exact matches
In this video, we look at how the MATCH function can find the position of an item in a list. Once you have a position, you can use it to get related values with INDEX.

See also 
How to use INDEX and MATCH

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you’ll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You’ll also learn how to troubleshoot, trace errors, and fix problems. Instant access.  See details here .

500 Formula Examples, thoughtfully explained.

Download 200+ Excel Shortcuts

Comments welcome! Read this before posting a question.

Topics

  • Video training
  • Function Guide
  • Formula Examples
  • Formula Criteria
  • Excel Shortcuts
  • Pivot Tables
  • 101 Excel Functions
  • Excel Tables
  • INDEX and MATCH
  • Excel Charts
  • Conditional Formatting
  • Custom Number Formats
  • Data Validation
  • Nested IF examples
  • Formula challenges
  • How-to videos
  • Excel glossary
  • Excel people
  • Excel books
  • Recent comments

Key functions

  • IF function
  • VLOOKUP function
  • S UMIFS function
  • COUNTIFS function
  • INDEX function
  • MATCH function
  • SUMPRODUCT function

Hi – I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.  Read more .

I felt compelled to send my thanks for your clear, informative, concise, and just very helpful videos. I have been thrust a data management assignment without training and far outside my usual duties, so these have been invaluable. -Zoë

Excel foundational video course

Excel Pivot Table video training course

Excel formulas and functions video training course

Excel Shortcuts Video Course

Excel Charts video training course

Video training for Excel Tables

Excel video training

Quick, clean, and to the point.

Learn more

Moved Permanently

The document has moved here .