An Excel Formula know how to re-arrange the data

[wilchong via OfficeKB.com]

The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D and E
In B3 down is: 35, 45, 13, 8 and 3.

The result which I want is to list the data E, D, C, A and B from the cell C3
to C7. I want a formula which know how to rank the data in which the Excel
formula know B has the highest value of 45 and E has the lowest value of 3.

Many thanks,

Wilchong

 

[T. Valko]

Try this entered in C3 and copied down:

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)),B$3:B$7,0))

If there are duplicate numbers then a different approach will be needed.

 

[wilchong via OfficeKB.com]

Dear T. Valko,
Many thanks for your suggested formular!

Really appreciate it!
Wilchong

Try this entered in C3 and copied down:

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)),B$3:B$7,0))

If there are duplicate numbers then a different approach will be needed.

The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D and E

[quoted text clipped - 10 lines]

Wilchong

 

[wilchong via OfficeKB.com]

Dear T. Valko
I have use your suggested formula and it has proven very efficient if there
is no repeat value in B col. May I ask how to revise your suggested formula
if A and B have the same value of 45?

Many thanks,
Wilson

Try this entered in C3 and copied down:

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)),B$3:B$7,0))

If there are duplicate numbers then a different approach will be needed.

The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D and E

[quoted text clipped - 10 lines]

Wilchong

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Dear T. Valko,
Many thanks for your suggested formular!

Really appreciate it!
Wilchong

Try this entered in C3 and copied down:

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)),B$3:B$7,0))

If there are duplicate numbers then a different approach will be needed.

The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D and E

[quoted text clipped - 10 lines]

Wilchong

 

[T. Valko]

Try this array formula** :

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7+ROW(B$3:B$7)/10^10,ROWS(C$3:C3)),B$3:B$7+ROW(B$3:B$7)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

Dear T. Valko
I have use your suggested formula and it has proven very efficient if
there
is no repeat value in B col. May I ask how to revise your suggested
formula
if A and B have the same value of 45?

Many thanks,
Wilson

Try this entered in C3 and copied down:

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7,ROWS(C$3:C3)),B$3:B$7,0))

If there are duplicate numbers then a different approach will be needed.

The 2 data sets are assumed running in A3 and in B3 down, viz.:
In A3 down is: A, B, C, D and E

[quoted text clipped - 10 lines]

Wilchong

 

[wilchong via OfficeKB.com]

Dear T.Valko,
Many thanks for your advice!

Wilchong

Try this array formula** :

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7+ROW(B$3:B$7)/10^10,ROWS(C$3:C3)),B$3:B$7+ROW(B$3:B$7)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Dear T. Valko
I have use your suggested formula and it has proven very efficient if

[quoted text clipped - 17 lines]

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Dear T.Valko,
Many thanks for your advice!

Wilchong

Try this array formula** :

=INDEX(A$3:A$7,MATCH(SMALL(B$3:B$7+ROW(B$3:B$7)/10^10,ROWS(C$3:C3)),B$3:B$7+ROW(B$3:B$7)/10^10,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Dear T. Valko
I have use your suggested formula and it has proven very efficient if

[quoted text clipped - 17 lines]

Wilchong