# Rank based on values in 2 colums

#### ssj011

##### New Member
Good Day,

Need the formula to rank the data based on the 2 columns

1. Rank should be for a combination of values in 2 different columns
2. Rank should be ascending order till the value in the D is not changed
3. If the value in the D column changes the rank should start again from 1
Regards
Sachin

#### Attachments

• 12.2 KB Views: 10

#### Peter Bartholomew

##### Well-Known Member
There must be something simpler?
I placed the data in a table because I failed to produce an answer as a single array.

#### vletm

##### Excel Ninja
ssj011
Rank
returns the rank of a number in a list of numbers.
...
Your shown C-column results (without those numbers) could get like this sample without any formulas.

#### Attachments

• 16.9 KB Views: 3

#### bosco_yip

##### Excel Ninja
Try,

In F2, formula copied down :

=IF(D2<>D1,1,IF(COUNTIFS(D\$2:D2,D2,E\$2:E2,E2)=1,LOOKUP(9^9,F\$1:F1)+1,""))

#### Excel Wizard

##### Active Member
Please try at
F1
=IF(D2=D1,IF(E2=E1,"",LOOKUP(9^9,F\$1:F1)+1),1)

or MS365 Spill Array
=LET(a,D2:E86,b,UNIQUE(a),c,INDEX(a,,1)&INDEX(a,,2),
d,SEQUENCE(ROWS(b))-MATCH(INDEX(b,,1),INDEX(b,,1),)+1,
IF(MATCH(c,c,)=SEQUENCE(ROWS(a)),XLOOKUP(c,INDEX(b,,1)&INDEX(b,,2),d),""))

#### Attachments

• 15.3 KB Views: 1