How to sort table using function/equation only

Question

How can I sort a table in sheet 1 like ``` A B C D E 3 7 3 6 5 ``` into another table in sheet 2 ``` A C E D B 3 3 5 6 7 ``` by using function only?

Accepted Answer

One really easy way to do it would be to just have a rank index and then use HLOOKUP to find the corresponding values:

```
=RANK(A4,$A$4:$E$4,1)
```

```
=IF(COUNTIF($A$1:A$1,A1)>1,RANK(A4,$A$4:$E$4,1)+COUNTIF($A$1:A$1,A1)-1,RANK(A4,$A$4:$E$4,1))
```

```
=HLOOKUP(COLUMN(),$A$2:$E$4,2,FALSE)
```

```
=HLOOKUP(COLUMN(),$A$2:$E$4,3,FALSE)
```

Solution