return address of found value with vlookup

S

sybmathics

Hi,

I'm searching in a large table for a specified date with vlookup. the
function returns a value form the 6th column in the lookup table, where the
last argument is set to TRUE.

My question now is: can excel also return the address of the cell with the
value that the vlookup returns?

Any help is greatly appreciated.

greets,


Sybolt
 
B

Bob Phillips

If you use MATCH on the first column instead of VLOOKUP, you will get the
index of the matching item within the table

=MATCH("some_val",M1:M100,0)



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
G

Guest

This will return the address.

=ADDRESS(MATCH(DATE(2007,1,25),$A$4:$A$503,1)+3,6)

You need to modify the +3 and the 6 at the end.

the +3 is the starting row for the match -1 hereI start on row 4 (A4)
therefore 4-1=3
the 6 is the 6th column across like the vlookup.

you may want to look at the last item in the match ,1) this is the match
type you indicate that for vlookup you use TRUE and I believe that this
corresponds to 1 in MATCH.
 
S

sybmathics

Working a bit with the match and indirect functions helped me solve the
problem.

Thanks a lot, the both of you.

cheers,

Sybolt
 

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

Top