How does RANK.EQ handle tied values?

When multiple values are identical, they all receive the same rank. The next distinct value skips ranks accordingly. For example, if two values tie for rank 1, the next different value receives rank 3 (not rank 2), reflecting that two positions were occupied at rank 1.

What does the order parameter actually control?

Order determines the ranking direction. Use order=0 or FALSE (default) to rank from highest to lowest, making the top score rank 1. Use order=1 or TRUE to rank from lowest to highest, making the lowest score rank 1. Choose based on your analysis goal: identifying winners or identifying improvement needs.

Can I rank values across multiple columns at once?

No, RANK.EQ ranks a single number against a single array. To rank across multiple columns, apply the formula to each column separately, or consolidate values into a helper column and rank that column.

Why would I use RANK.EQ instead of LARGE or SMALL?

Use RANK.EQ when you have a specific score and want to know its position. Use LARGE or SMALL when you want to find the nth-highest or nth-lowest score. They answer different questions: RANK.EQ asks 'where does this score rank?' while LARGE/SMALL ask 'what's the top score?'

RANK.EQ function

Returns the position of a number in a sorted list, useful for finding how a value ranks among peers in descending (or ascending) order.

=RANK.EQ(number, ref, [order])

Generate a RANK.EQ formula

Describe what you need. The generator will reach for RANK.EQ where RANK.EQ is the right tool, and tell you when it is not.

Arguments

How RANK.EQ reads its arguments

Argument	Required	Description
number	Required	The value to rank; required, must be numeric or a cell reference to a numeric value. RANK.EQ calculates its theoretical position even if the exact value isn't found in the range.
ref	Required	The array or range of values to rank within; required, must contain at least one numeric value. Can be a range, array constant, or array formula result.
order	Optional	Optional; 0 or FALSE for descending rank (highest value = rank 1, the default), 1 or TRUE for ascending rank (lowest value = rank 1). Omit to use descending behavior.

Returns

A positive integer representing the rank position of the number within the sorted reference range.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find Alice's rank among Math Quiz 1 scores

Student	Subject	Assignment	Score	Max
Alice	Math	Quiz 1	95	100
Bob	Math	Quiz 1	87	100
Carol	Math	Quiz 1	92	100
David	Math	Quiz 1	85	100
Eve	Math	Quiz 1	95	100

=RANK.EQ(95, {95;87;92;85;95})

Result: 1

Alice's Math score of 95 is the highest among the five scores (95, 87, 92, 85, 95). Even though Eve also scored 95, both students receive rank 1 because RANK.EQ assigns the same rank to tied values. The next distinct score (92) would receive rank 3, not rank 2.

2. Compare ranking with ascending order to identify the lowest scorer

Student	Subject	Assignment	Score	Max
Alice	Science	Quiz 1	88	100
Bob	Science	Quiz 1	91	100
Carol	Science	Quiz 1	85	100
David	Science	Quiz 1	90	100
Eve	Science	Quiz 1	89	100

=RANK.EQ(85, {88;91;85;90;89}, 1)

Result: 1

With ascending order (order=1), the lowest score ranks first. Carol's Science score of 85 is the minimum among these five scores (88, 91, 85, 90, 89), so it receives rank 1. This reverses the default behavior where high scores rank first.

3. Rank a strong mid-range performer on the Science quiz

Student	Subject	Assignment	Score	Max
Alice	Science	Quiz 1	88	100
Bob	Science	Quiz 1	91	100
Carol	Science	Quiz 1	85	100
David	Science	Quiz 1	90	100
Eve	Science	Quiz 1	89	100

=RANK.EQ(90, {88;91;85;90;89})

Result: 2

David's Science score of 90 is the second-highest among the five students, ranking just behind Bob's 91. RANK.EQ with default descending order correctly places this strong performance at rank 2.

Common errors

Which RANK.EQ error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The order parameter is text or an invalid type, such as =RANK.EQ(95, A2:A6, "descending") or =RANK.EQ(95, A2:A6, TRUE) where TRUE is misspelled as a string.	Replace text values with 0 for descending or 1 for ascending; use actual Boolean TRUE/FALSE values or numeric 0/1.
#NUM!	The order parameter is a number other than 0 or 1, such as =RANK.EQ(95, A2:A6, 2) or =RANK.EQ(95, A2:A6, -1).	Change the order argument to only 0 (descending, default), 1 (ascending), FALSE (descending), or TRUE (ascending).
#REF!	The reference range points to deleted cells or an invalid range, such as when the original data has been removed or the range reference is broken by a deleted column.	Verify the range still exists; reselect the valid range or use absolute references ($A$2:$A$6) to protect against accidental changes.

Tips and when to use something else

Tied values receive the same rank, and subsequent ranks skip accordingly—if two values tie for rank 1, the next different value is rank 3, not rank 2.
Use LARGE or SMALL to find the nth-highest or nth-lowest value directly; use RANK.EQ when you need to rank a specific value you already have in hand.
In descending order (default), the highest value ranks 1st; in ascending order, the lowest value ranks 1st. Choose based on whether you're identifying top performers or flagging those who need improvement.
Combine RANK.EQ with MATCH or XLOOKUP to create labeled ranking results (e.g., 'Alice is rank 1' by pairing the rank number with the student's name).

Related functions

LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.MATCHMATCH returns the relative position of a lookup value within a one-dimensional range, optionally using exact, less-than, or greater-than matching.COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.

Frequently asked questions

How does RANK.EQ handle tied values?: When multiple values are identical, they all receive the same rank. The next distinct value skips ranks accordingly. For example, if two values tie for rank 1, the next different value receives rank 3 (not rank 2), reflecting that two positions were occupied at rank 1.
What does the order parameter actually control?: Order determines the ranking direction. Use order=0 or FALSE (default) to rank from highest to lowest, making the top score rank 1. Use order=1 or TRUE to rank from lowest to highest, making the lowest score rank 1. Choose based on your analysis goal: identifying winners or identifying improvement needs.
Can I rank values across multiple columns at once?: No, RANK.EQ ranks a single number against a single array. To rank across multiple columns, apply the formula to each column separately, or consolidate values into a helper column and rank that column.
Why would I use RANK.EQ instead of LARGE or SMALL?: Use RANK.EQ when you have a specific score and want to know its position. Use LARGE or SMALL when you want to find the nth-highest or nth-lowest score. They answer different questions: RANK.EQ asks 'where does this score rank?' while LARGE/SMALL ask 'what's the top score?'

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17