Why do identical formulas sometimes act differently?

[Scarlett O]

I applied identical nested formulas to different areas of the same worksheet
but I didn't get identical results. Why? I used RANK on data in cell range
K5:K54 asking for results in M5:M54. Then I used IF, COUNTIF and MATCH
functions in subsequent formulas to return results from M5:M54,L5:L54
(column M showed the ranks and column L showed the items) in Q5:Q9 showing
the top five ranking items. This worked well. But when I used identical
formulas on the same worksheet just changing the column identifiers, it only
returned the ranks instead of the items. In the 2nd instance, I replaced K
with AA showing results in AC instead of M. This returned correct results.
But when I replaced M with AC and L with AB, returning results in Q15:Q19, it
only showed the rank without the item. Also in the 2nd instance, it showed no
number 1 rank but showed the number 2 rank twice and then completed ranks 3,
4 and 5. Any ideas why this happened?

 

[Toppers]

Hi,
I can't offer an aswer without seeing the formulae (although I would
double check cell references - relative/absolute) but is not possible to
use LARGE to find the top five values? Or I have misunderstood what you are
doing?

 

[K Dales]

The last question is the easiest to answer: When there is a "tie", rank
assigns both (or all of the tied values) an identical rank.

It is harder to know the answer to the other part of the question without
seeing both your formulas and the data you are trying to rank. The rank is
working, apparently, so it must be in the IF, COUNTIF, and/or MATCH
functions. But without seeing the actual functions (what are you trying to
match) and having the list of values in the lookup range, I can't say why it
is not returning a value.