return address of found value with vlookup

  • Thread starter Thread starter sybmathics
  • Start date Start date
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
 
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)
 
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.
 
Working a bit with the match and indirect functions helped me solve the
problem.

Thanks a lot, the both of you.

cheers,

Sybolt
 
Back
Top