return address of found value with vlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sybmathics - 14 Jan 2007 10:13 GMT

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

Reply to this Message

Bob Phillips - 14 Jan 2007 11:50 GMT

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)

Signature

HTH

Bob Phillips

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

> Hi,
>
[quoted text clipped - 10 lines]
>
> Sybolt

Reply to this Message

Martin Fishlock - 14 Jan 2007 12:41 GMT

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.

Signature

Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

> Hi,
>
[quoted text clipped - 10 lines]
>
> Sybolt

Reply to this Message

sybmathics - 14 Jan 2007 15:57 GMT

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

Thanks a lot, the both of you.

cheers,

Sybolt

Reply to this Message

Rate this thread: