In following cells

[A1] 1 [B1] 22 [C1] 2
[A2] 2 [B2] 25 [C2] 1
[A3] 3 [B3] 22 [C3] 1, I would like it equals to 3

[C1]=OFFSET($A$1,MATCH(LARGE(B:B,$A1),B:B,0)-1,0)
[C2]=OFFSET($A$1,MATCH(LARGE(B:B,$A2),B:B,0)-1,0)
[C3]=OFFSET($A$1,MATCH(LARGE(B:B,$A3),B:B,0)-1,0)

When the MATCH function is being used, the OFFSET will always locate the
first value in cell A1, and skip the second value in cell A3, since both
values in B colume are equal to 22. Does anyone know how to modify the code
in C3? so the cell C3 can display the second value in cell A3.
Thank you for any suggestion
Eric

See one response to your multi-post in .worksheet.functions
Pl do not multi-post
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
In following cells

[A1] 1 [B1] 22 [C1] 2
[A2] 2 [B2] 25 [C2] 1
[A3] 3 [B3] 22 [C3] 1, I would like it equals to 3

[C1]=OFFSET($A$1,MATCH(LARGE(B:B,$A1),B:B,0)-1,0)
[C2]=OFFSET($A$1,MATCH(LARGE(B:B,$A2),B:B,0)-1,0)
[C3]=OFFSET($A$1,MATCH(LARGE(B:B,$A3),B:B,0)-1,0)

When the MATCH function is being used, the OFFSET will always locate the
first value in cell A1, and skip the second value in cell A3, since both
values in B colume are equal to 22. Does anyone know how to modify the code
in C3? so the cell C3 can display the second value in cell A3.
Thank you for any suggestion
Eric