How to skip a column with a hlookup

[BlindGuardian117]

I have 1 column with a list of brand names and an hloopup with every letter of the alphabet and numbers that correspond with how often the letter appears in a brand name. I’m trying to do a top 5 and bottom 5 for most and least appearing letters. Unfortunately, there are ties that I want to skip over. I don’t know how to get hlookup to skip and grab the next instance.

IE: LIBREOFFICE has 2 E, 2 F, 2 I so E appears twice and I not at all.
E F I
2 2 2

Top 1: E
Top 2: E
Top 3: E

and it needs to be
Top 1: E
Top 2: F
Top 3: I

I don’t know what you guys need from me. Everything is pretty simple and used as recommend in the docs.

A1 - Z1 are the letters
A2 - Z2 are the numbers
AB2:AB2021 is the brand names

A1 =SUMPRODUCT(LEN(AB2:AB2021)-LEN(SUBSTITUTE(AB2:AB2021,“A”,"")))
For searching the list of brand names per each letter

AF11 =LARGE(A1:Z2,1)
For getting the number that appears the most

AG11 =HLOOKUP(AF11, A1:Z2,2,0)
For getting the alphabet character that appears the most
examplefile.ods (13.9 KB)

[Zizi64]

Please upload an ODF type sample file here.

[Steph1]

To avoid ties, you could add, to each cell of the first row, the quantity COLUMN()/1000. This way, E1 will contain 2.005 and F1 will contain 2.006.
You can then use the function RANK to rank the frequencies.

[BlindGuardian117]

Sorry added it to the original post.

[schiavinatto]

Following @Steph1’s tip, here’s the changed file…
examplefile_GS.ods (14,8,KB)

[BlindGuardian117]

This works perfectly! Thank you!