Return most frequently occurring text if begins with specific value

This tutorial shows how to get the most frequently occurring text which begins with specific a value using an Excel formula, with the INDEX, MATCH, MODE.SNGL, IF, LEFT and COUNTIF functions

EXCEL FORMULA 1. Return most frequently occurring text if begins with specific value

EXCEL

Hard coded formula
Return most frequently occurring text if begins with specific value
Cell reference formula
Return most frequently occurring text if begins with specific value
=INDEX(B5:B10,MATCH(MODE.SNGL(IF(LEFT(B5:B10,1)="b",COUNTIF(B5:B10,"<"&B5:B10),"")),COUNTIF(B5:B10,"<"&B5:B10),0))
=INDEX(B8:B13,MATCH(MODE.SNGL(IF(LEFT(B8:B13,1)=C5,COUNTIF(B8:B13,"<"&B8:B13),"")),COUNTIF(B8:B13,"<"&B8:B13),0))
GENERIC FORMULA

=INDEX(range,MATCH(MODE.SNGL(IF(LEFT(range,1)=value,COUNTIF(range,"<"&range),"")),COUNTIF(range,"<"&range),0))

ARGUMENTS
range: The range from which to return the most frequently occurring text that begins with a specific value.
value: The beginning value to test for.

GENERIC FORMULA

=INDEX(range,MATCH(MODE.SNGL(IF(LEFT(range,1)=value,COUNTIF(range,"<"&range),"")),COUNTIF(range,"<"&range),0))

ARGUMENTS
range: The range from which to return the most frequently occurring text that begins with a specific value.
value: The beginning value to test for.

EXPLANATION

This formula uses the INDEX, MATCH, MODE.SNGL, IF, LEFT and COUNTIF functions to extract the most frequently occurring text if it begins with a specific value. Given that this is an array formula, once the formula is entered you are required to press Ctrl + Shift + Enter at the same time, which will enclose the formula in { } signs.
Please note that if there is only one occurrence of the most frequently occurring text in the list the formula will return an #N/A error.

Click on either the Hard Coded or Cell Reference button to view the formula that has the beginning value directly entered into the formula or referenced to a specific cell.

RELATED TOPICS

Related Topic Description Related Topic and Description
Return most frequently occurring text How to return the most frequently occurring text
Count most frequently occurring text How to count the most frequently occurring text

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
IF Function The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE
COUNTIF Function The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria
INDEX Function The Excel INDEX function returns a value that is referenced from a specified range
MATCH Function The Excel MATCH function searches for a specified value in a range and returns its position relative to the range
LEFT Function The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side