Is there any formula which can give cell address of occurence of a
vlookup formula.
Thxs

wrote...
Is there any formula which can give cell address of occurence of a
vlookup formula.

If VLOOKUP(x,Range,n) evaluates to a cell in Range, then

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

gives its address.

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


Harlan Grove wrote:
wrote...
Is there any formula which can give cell address of occurence of a
vlookup formula.

If VLOOKUP(x,Range,n) evaluates to a cell in Range, then

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

gives its address.

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

On 25 Oct 2005 05:45:19 -0700, wrote:

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




MATCH returns the relative position of an item in an array that matches a
specified value.

The array to which the MATCH function is applied is generated by the inner
INDEX function which generates an array consisting of the first column of your
Range.

The outer INDEX function refers to the cell in column 2 that is in the same
relative position as the MATCH in column 1.

The CELL function then returns the reference of that outer INDEX function.


--ron

many thxs for your explanation
Ron Rosenfeld wrote:
On 25 Oct 2005 05:45:19 -0700, wrote:

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




MATCH returns the relative position of an item in an array that matches a
specified value.

The array to which the MATCH function is applied is generated by the inner
INDEX function which generates an array consisting of the first column of your
Range.

The outer INDEX function refers to the cell in column 2 that is in the same
relative position as the MATCH in column 1.

The CELL function then returns the reference of that outer INDEX function.


--ron