# Thread: Ranking based on a cell value

1. I have a set of data...

Trigger cell Total Rank Rank Males Rank Females
Concept 1 3 4 1
Concept 2 2 2 2
Concept 3 4 1 3
Concept 4 1 3 4

In the trigger cell i want to enter 1, 2 or 3 and have the 4 rows below sort from lowest to highest (1 to 4). So if the trigger cell (A1) has a 1 in it i want to return...

Concept 4 1
Concept 2 2
Concept 1 3
Concept 3 4

If the trigger cell has a 2 then i want...

Concept 3 1
Concept 2 2
Concept 4 3
Concept 1 4

Is this possible to do in formulas? Thank you for the help.

2. Perhaps the formula posted by Steve in Post 770792 might help you Excel formula,If and Rank

Regards,

Tom Duthie

3. If your data starts in cell A1 and cell E1 contains the number:

=INDEX(\$A\$1:\$A\$4,MATCH(SMALL(OFFSET(\$A\$1:\$A\$4,0,E1 ),ROW(\$A\$1:\$A\$4)),OFFSET(\$A\$1:\$A\$4,0,E1),0))

(Array formula, enter using control+shift+enter)

4. Sorry, I'm not quite sure how to use this formula. I'm uploading a file and put the trigger cell in A1. The formula is in F3. Thank you for the help.

5. In G2:
=INDEX(B22,A1)
In G3-G6: 1,2,3,4, respectively

In F3:
=INDEX(\$A\$3:\$A\$6,MATCH(G3,INDEX(\$B\$3:\$D\$6,0,\$A\$1), 0))

Copy F3 to F4:F6

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•