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


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.