On most websites, you will see a section of the most frequently asked questions. They use some programming code to evaluate frequently appearing text and on the basis of that, they add those questions to category.
Well, the same thing can be done on excel too. If you want to look up the most frequently appearing string in a range, this article is for you.
Generic Formula
| =INDEX(range,MODE(MATCH(range,range,0))) |
Range: This is the range from which you want to lookup frequently appearing text.
Let's see an example to see how we can retrieve frequently appearing text in Excel.
Example: Get the name of the player from each year that wins most of the matches.
Here, I have a record of pool matches. Each year, the company holds 9 matches. We want the names of the winners who won most of the matches.
Write this formula in B13 and copy it in the right of the cell.
| =INDEX(B3:B12,MODE(MATCH(B3:B12,B3:B12,0))) |
As you can see, the most frequent names are retrieved in the cells.
How does it work?
Here, we have used a combination of INDEX, MODE and MATCH function.
As usual, the formula starts working from the inside. The innermost function is the MATCH function. We have supplied the same B3:B12 range as the lookup value and lookup array with an exact match. This allows the MATCH function to return an array. The array we get indexes of the first appearance of each value. This means if Jack has appeared on index 1 then on every appearance of jack array will have 1. In this case, we get an array {1;1;1;4;4;1;4;4;1;10}.
Next, this array is encapsulated in the MODE function. The MODE function returns the most frequently appearing number. In our case, the MODE function returns 1. This will be the row number for the INDEX function.
Next, we have the INDEX function that has an array as B3:B12 and row number 1 to retrieve the value from. Hence, we got Jack for the year 2016.
Note: This formula works in a one-dimensional range only. I mean, the range you select must be rows only or columns only. If you provide a two-dimensional array/range, it will return #N/A.
So yeah guys, this is how you get the most frequently appearing text in range. This can be used in excel 2007, 2010, 2013, 2016, and 365. If you have any doubts regarding this article or any other excel topic, ask me in the comments section below.
Get most frequently appearing text in Excel
Related Articles:
Lookup Frequently Appearing Text with Criteria in Excel | To get the most frequently appearing text in excel with criteria you just need to put an if condition before match function.
Retrieving the First Value in a List that is Greater / Smaller than a Specified Value | Learn how to lookup first value that is greater than a specified value. The generic formula to lookup is...
How to retrieve the entire row of a matched value| It is easy to look up and retrieve the entire row from a table. Let's see how can we do this lookup.
How to Retrieve Latest Price in Excel | To look up the latest price from a range you must have your data sorted. Once sorted use this formula...
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
