You are currently viewing How to lookup frequently appearing text in Excel

How to lookup frequently appearing text in Excel

The maximum number in which a text is repeated in a string is normally referred to as the frequently occurring text. There are many ways you can find them, and some of those include using the MODE, INDEX, and MATCH functions. The MATCH function gets a match of the range and itself. The process uses three parameters, and the first two are where you want to locate the most frequent text while the last one is zero.

Using those parameters ensures you get an exact match. It is important to know that the formula starts working from the inside. The match function shows each repeated text and shows it through an array or range. The MODE function, on the other hand, shows one of the numbers or text that is frequently repeated. The Index function uses the first two to get the frequently appearing text in excel.

Note that the formula discussed above is always applicable in a one dimension range only. Any user with excel, 365, 2016, 2013, 2010, and 2007 can use the formula. Below are steps one can follow to lookup the frequently appearing text in excel.

 

Steps to look up frequently appearing text in Excel

1. Get a list of names in a column in excel. For example, list 21 random names of cars and repeat a few names

2. Use the MATCH function to convert the names of the cars into numbers since you need mode in terms of a number. Replace this formula with your ranges =INDEX(range,MODE(MATCH(range,range,0)))

3. Use the control shift and down arrow f4

4. Write this formula on any cell or on the formula bar =INDEX(A1:A21,MODE(MATCH(A1:A21,A1:A21,0)))

5. Click on enter, and you get the name of the car that is frequently appearing

 

Method 2

1. Enter the formula INDEX (B2:B21, MODE (MATCH B2:B21,B2:B21, 0)

This formula is equal to the one outlined above, for the results are similar.

2. Using the example with a list of car names, use the MATCH function to get the position of a value in a given range (Mercedes Benz is found at position 2 in the range B1; B21)

3. Set the third argument to 0 to get an exact match

4. Add the MODE function, which replaces the B21 with B1:B21

5. Press CTRL+SHIFT+ENTER

6. The formula occurs at the formula bar and will be enclosed with brackets

7. Use the INDEX function to get the second car name to return to the B1:B21 range, which is the most frequently occurring word.

Conclusion:

Instead, never type the formula yourself; instead, use the functions mentioned earlier, and the formula will create itself. All the formulas are similar and result in the same result. The COUNTIF function could be used to get the number of times a word is frequently used, but the best is using the formula discussed above with the IDEX, MATCH, and MODE functions.