Dynamic Arrays & Lookup
Find the Most Common Text Values (#1 Categorical Mode & Ranked Runner-Ups)
Extract the most frequent text label and second-most common category—where numeric MODE returns #N/A—using COUNTIF frequencies, MAX, MATCH, INDEX, and FILTER.
Why MODE.SNGL fails on text columns and how frequency ranking works
Built-in MODE and MODE.SNGL ignore text strings completely and return #N/A when applied to ticket tags, defect reasons, or product names.
Adding a COUNTIF($A$2:$A$8, A2) frequency column lets INDEX(A2:A8, MATCH(MAX(B2:B8), B2:B8, 0)) retrieve the #1 most common text label immediately, while filtering out the #1 winner with FILTER(A2:A8, A2:A8 <> winner) extracts #2, #3, and top-N categorical leaders cleanly.
| Rank | Ticket Category | Occurrences | Extraction Logic |
|---|---|---|---|
| #1 Top Category | Billing | 3 | INDEX(A2:A8, MATCH(MAX(B2:B8), B2:B8, 0)) |
| #2 Runner-Up | API | 2 | Top match after FILTER(..., A2:A8 <> "Billing") |
| Remaining | SSO, Export | 1 each | Tied at frequency 1 |
#1 Most Common Text Value
Finds the maximum occurrence count in B2:B8 and returns the corresponding text category from A2:A8.
=INDEX(A2:A8, MATCH(MAX(B2:B8), B2:B8, 0))#2 Second Most Common Text Value via LET + FILTER
Excludes all rows belonging to the #1 category first so duplicate rows of the winner cannot occupy rank #2 as well.
=LET(topCat, INDEX(A2:A8, MATCH(MAX(B2:B8), B2:B8, 0)), remCats, FILTER(A2:A8, A2:A8 <> topCat), remCnts, FILTER(B2:B8, A2:A8 <> topCat), INDEX(remCats, MATCH(MAX(remCnts), remCnts, 0)))Try the example
Use the original values or change them to test your understanding.