Help Needed with Formula: FIND the most common number in a list that follows a given value

[D_Spark]

I have a workbook Range (A1:A500)........text values
Cell C1 represent the last value in range (A1:A500)
I need help with creating a formula in cell D1.
This will return the most common text value in the range that follows an occurrence determined by C1.


eg if C1 was to equal "Paul" then D1 would show "Helen", as "Helen" is the most common text value that follows a text value of "Paul" in the range

A1 = Paul
A2 = Helen
A3 = Paul
A4 = Duncan
A5 = Paul
A6 = Helen
A7 = Duncan
A8 = Duncan
A9 = Paul
A10 = Helen
A11 = Paul
A12 = Helen
A13 = Paul
A14 = Duncan

 

[sergioMabres]

Hi Spark
Your problem seems to be of the "how to rank based on number of occurrences" and is been ask many times, you could use the formula that user mattaustin01 posted that works fine to find most common text value

{=INDEX(yourrange,MATCH(MAX(COUNTIF(yourrange,yourrange)),COUNTIF(yourrange,yourrange),0))}

I think now you only need to find the range below Paul and you are done
Cheers
Sergio

 

[sergioMabres]

I see you really mean "immediately follows" no just "follows" so I guess you need a UDF
Cheers
Sergio

 

[D_Spark]

I see you really mean "immediately follows" no just "follows" so I guess you need a UDF
Cheers
Sergio

thanks Sergio

Ive needed to move the cells on the worksheet around, but the formula below seems in cell L169 seems to work to predict

=IFERROR(MODE(IF($J$2:J167=J168,$J$3:J168)),INDEX($J$2:J168,MAX(IF($J$2:J167=J168,ROW($J$3:J168)))))



How can I use the above and enter a formula into L170 to calculate the 2nd most common occurrence, not the most common?