Summary

To break ties, you can use a helper column and the COUNTIF function to adjust values so that they don't contain duplicates, and therefore won't result in ties. In the example shown, the formula in D5 is:

=C5+(COUNTIF($C$5:C5,C5)-1)*0.01

Context

Sometimes, when you use functions like SMALL, LARGE, or RANK to rank highest or lowest values, you end up with ties, because the data contains duplicates. One way to break ties like this is to add a helper column with values that have been adjusted, then rank those values instead of the originals.

In this example, the logic used to adjust values is random - the first duplicate value will "win", but you can adjust the formula to use logic that fits your particular situation and use case.

Generic formula

=A1+(COUNTIF(exp_rng,A1)-1)*adjustment

Explanation 

At the core, this formula uses the COUNTIF function and an expanding range to count occurrences of values. The expanding reference is used so that COUNTIFS returns a running count of occurrences, instead of a total count for each value:

COUNTIF($C$5:C5,C5)

Next, 1 is subtracted from the result (which makes the count of all non-duplicate values zero) and the result is multiplied by 0.01. This value is the "adjustment", and intentionally small so as not to materially impact the original value.

In the example shown, Metrolux and Diamond both have the same estimate of $5000. Since Metrolux appears first in the list, the running count of 5000 is 1 and is cancelled out by subtracting 1, so the estimate is remains unchanged in the helper column:

=C8+(COUNTIF($C$5:C8,C8)-1)*0.01
=C8+(1-1)*0.01
=C8+0
=C8

However, for Diamond, the running count of 5000 is 2, so the estimate is adjusted:

=C11+(COUNTIF($C$5:C11,C11)-1)*0.01
=C11+(2-1)*0.01
=C11+1*0.01
=C11+0.01

Finally, the adjusted values are used for ranking instead of the original values in columns G and H. The formula in G5 is:

=SMALL($D$5:$D$12,F5)

The formula in H5:

=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))

For detailed explanation of these formulas, see this example.

Temporary helper column

If you don't want to use a helper column in the final solution, you can use a helper column temporarily to get calculated values, then use Paste Special to convert values "in place" and delete the helper column afterwards. This video demonstrates the technique.

Related formulas

Excel formula: nth smallest value

nth smallest value

In this example, the goal is to extract the 3 best race times for each name from the 5 race times that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the fastest time, the 2nd fastest time, and the 3rd fastest time. This problem can be solved with the SMALL...
Excel formula: nth largest value

nth largest value

In this example, the goal is to extract the top 3 quiz scores for each name from the 5 scores that appear in columns C, D, E, F, and G. In other words, for each name listed, we want the best score, the 2nd best score, and the 3rd best score. This problem can be solved with the LARGE function. Note...
Excel formula: Sum bottom n values

Sum bottom n values

In this example, the goal is to sum the smallest n values in a set of data, where n is a variable that can be easily changed. At a high level, the solution breaks down into two steps (1) extract the n smallest values from the data set and (2) sum the extracted values. This problem can be solved...
Excel formula: Find lowest n values

Find lowest n values

The SMALL function retrieves the smallest values from data based on a given rank. For example: =SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest In the worksheet shown, the rank (which is provided to SMALL as the k argument) comes from numbers in column E...

Related functions

Excel SMALL function

SMALL Function

The Excel SMALL function returns a numeric value based on its position in a list when sorted by value in ascending order. In other words, SMALL can return the "nth smallest" value (1st smallest value, 2nd smallest value, 3rd smallest value, etc.) from a set of numeric data.
Excel INDEX function

INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....
Excel MATCH function

MATCH Function

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, MATCH is combined with the...

Related videos

How to get nth values with SMALL and LARGE

In this video we'll look at how to calculate the "nth" smallest or largest values in a range using the SMALL or LARGE function s. This would be, for example, the 1st, 2nd, and 3rd smallest or largest values. In this first sheet, we have a list of students with five test scores. I'll use the LARGE...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Videos

Training