cell address of occurence of a vlookup formula

  • Thread starter Thread starter albertnhc
  • Start date Start date
A

albertnhc

Is there any formula which can give cell address of occurence of a
vlookup formula.
Thxs
 
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.
 
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
 
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
 
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
 
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
 
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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use & with two formulas 2
Searching the formula (not the value) for a text string 1
VLOOKUP 2
EXCEL - IF(IFERROR(VLOOKUP question 0
Need VLOOKUP to Work Two Ways 0
Help With VLOOKUP 2
VLOOKUP return cell location and hyperlink 0
Dynamic col_index_num in VLOOKUP? 5

Back
Top