return address of found value with vlookup

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

 

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)

"sybmathics" <> wrote in message
news:45aa0222$0$25996$4all.nl...
> 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
>
>
>
>
>

 

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.

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


"sybmathics" wrote:

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

 

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

Thanks a lot, the both of you.

cheers,

Sybolt