Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Vlookup Examples in Excel
  • How to get last column number in range in Excel
  • Count unique text values with criteria
  • How to use Excel COLUMN Function
  • How to get first column number in range in Excel

Data Analysis Examples

  • Chart Axes in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Subtotal function in Excel
  • Filter Data Based on Date in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation number multiple 100
  • Excel Data validation date in next 30 days
  • Excel Data validation must contain specific text
  • Data validation must not exist in list

Extract most frequently occurring text in Excel

by

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.

Formula

=INDEX(range,MODE(MATCH(range,range,0)))

Explanation

In the example shown, the formula in H5 is:

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

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

Worked Example:   9 Mathematical Computations Example using Excel Statistical Function

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.

Worked Example:   How to calculate two-way lookup VLOOKUP in Excel Table

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.

Worked Example:   How to get random value from list or table in Excel

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.

Post navigation

Previous Post:

Join cells with comma in Excel

Next Post:

Find most frequent text within a range with criteria in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract domain name from URL in Excel
  • Extract word that begins with specific character in Excel
  • NUMBERVALUE function: Description, Usage, Syntax, Examples and Explanation
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • MID, MIDB functions: Description, Usage, Syntax, Examples and Explanation
  • Add workdays to date custom weekends in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • Add months to date in Excel
  • How to get same date next year or previous year in Excel
  • HOUR function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate principal for given period in Excel
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate periods for annuity in Excel
  • How to calculate simple interest in Excel
  • How to Calculate Tax Rates in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles