PC Review


Reply
Thread Tools Rate Thread

return address of found value with vlookup

 
 
sybmathics
Guest
Posts: n/a
 
      14th Jan 2007
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 With Quote
 
 
 
 
Bob Phillips
Guest
Posts: n/a
 
      14th Jan 2007
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" <(E-Mail Removed)> wrote in message
news:45aa0222$0$25996$(E-Mail Removed)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
>
>
>
>
>



 
Reply With Quote
 
 
 
 
=?Utf-8?B?TWFydGluIEZpc2hsb2Nr?=
Guest
Posts: n/a
 
      14th Jan 2007
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
>
>
>
>
>
>

 
Reply With Quote
 
sybmathics
Guest
Posts: n/a
 
      14th Jan 2007
Working a bit with the match and indirect functions helped me solve the
problem.

Thanks a lot, the both of you.

cheers,

Sybolt


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
If no value found in vLookup, I need to return a NULL to the Cell Ben Microsoft Excel Discussion 4 15th Nov 2010 06:51 PM
vlookup function to return the cell address of the found item skiing Microsoft Excel Discussion 2 9th Jul 2008 04:27 PM
How to return the address of a cell found via VLOOKUP? artisdepartis Microsoft Excel Programming 3 6th Jul 2007 10:54 AM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( =?Utf-8?B?T3Nv?= Microsoft Excel Worksheet Functions 2 26th Jan 2005 07:56 AM
"not all code paths return a value" when throwing exception, can't return a value n_o_s_p_a__m Microsoft C# .NET 5 23rd Jul 2003 08:56 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:27 PM.