Thxs a lot Ron!
But can you pls explain to me step by step the reasoning behind the
formula as I'm not used to complex formula with index & match.
Hope to hear from u again



Ron Rosenfeld wrote:
On 25 Oct 2005 02:49:29 -0700, wrote:











q 2.00
y 2.00
q 2.00
x 1.00
q 3.00
r 3.00
s 3.00
t 3.00
u 3.00
v 3.00

Range = A1:B10

y 2.00 ok
address $B$10 - wrong address with proposed formula

Harlan,
Thxs a lot for your reply - but I'm getting a incorrect address (should
have been B2)
Could you pls help


The MATCH portion of Harlan's formula assumes that Column 1 of Range is sorted
in ascending order. Since yours obviously is not sorted that way, his formula
should have read:

CELL("Address",INDEX(Range,MATCH(x,INDEX(Range,0,1 ),0),n))

or, in your instance:

=CELL("Address",INDEX($A$1:$B$10,MATCH("y",INDEX($ A$1:$B$10,0,1),0),2))


--ron