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
"wilchong via OfficeKB.com" <u43231@uwe wrote in message
news:84c3667d16b11@uwe...
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
T. Valko wrote:
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
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200805/1