Referring to Excel General Question 12/13/2006 6:43 AM PST
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

One way is to use a helper col to break the ties,
then point the OFFSET at this col, eg:

In D1:
=IF(B1="","",B1-ROW()/10^10)

In C1:
=OFFSET($A$1,MATCH(LARGE(D:D,$A1),D:D,0)-1,0)

Select C1:D1, copy down as far as required
Col C will return the required results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:
Referring to Excel General Question 12/13/2006 6:43 AM PST
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

Thank you very much for suggestion
Eric

You're welcome, Eric !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote in message
...
Thank you very much for suggestion
Eric