cell address of occurence of a vlookup formula

A

albertnhc

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

Harlan Grove

Is there any formula which can give cell address of occurence of a
vlookup formula.
Click to expand...

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.
 
T

transferxxx

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
 
R

Ron Rosenfeld

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
Click to expand...
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
 
T

transferxxx

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
T

transferxxx

many thxs for your explanation
Ron said:
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
Click to expand...
 
Top