software

I am wondering if there is a way to return a cell reference by having excel
look at a table with certain parameters. For instance...
If there were a table with repeated dates such as:
Feb 12 1500 1530
Feb 12 1745 1750
Feb 13 1215 1245
Feb 13 1610 1700
Feb 13 1850 1900
Feb 14 1050 1140
Is there a way to return the cell number (ie A6) for the first instance of
Feb 14?
Thanks in advance,
Hurton

Have a look in the help index for VLOOKUP

--
Don Guillett
SalesAid Software

quot;Hurtonquot; gt; wrote in message
...
gt;I am wondering if there is a way to return a cell reference by having excel
gt; look at a table with certain parameters. For instance...
gt; If there were a table with repeated dates such as:
gt; Feb 12 1500 1530
gt; Feb 12 1745 1750
gt; Feb 13 1215 1245
gt; Feb 13 1610 1700
gt; Feb 13 1850 1900
gt; Feb 14 1050 1140
gt; Is there a way to return the cell number (ie A6) for the first instance of
gt; Feb 14?
gt; Thanks in advance,
gt; Hurton
The MATCH( ) function will tell you the relative position of a matched
item in a list - you would need to add to this the start row if the
list did not start on row 1.

Hope this helps.

Pete
Why do you need the cell reference, is that your final aim or do you
need it for something else?--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=513798I was aiming to put this into a vlookup to find the first co ordinate for the
table array if that is possible.

Hurton

quot;daddylonglegsquot; wrote:

gt;
gt; Why do you need the cell reference, is that your final aim or do you
gt; need it for something else?
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=513798
gt;
gt;


Index can give you a cell reference, e.g.

=INDEX(A1:A20,MATCH(DATE(2006,2,14),A1:A20,0)):B20

will give you a range from the first cell in A1:A20 with the correct
date to B20, so if 14th february 2006 is first found in A14, this will
give you the range
A14:B20, you could then use this as your range in a VLOOKUP--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=513798Thanks for the help. I played around with it and got it to return the values
that i need. My next question is how to get that into a vlookup. I tried
putting the formula in as a reference for the table array but excel wouldn't
accept the formula.

Any advice?

Hurton

quot;daddylonglegsquot; wrote:

gt;
gt; Index can give you a cell reference, e.g.
gt;
gt; =INDEX(A1:A20,MATCH(DATE(2006,2,14),A1:A20,0)):B20
gt;
gt; will give you a range from the first cell in A1:A20 with the correct
gt; date to B20, so if 14th february 2006 is first found in A14, this will
gt; give you the range
gt; A14:B20, you could then use this as your range in a VLOOKUP
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=513798
gt;
gt;


What formula did you try?--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=513798The index/match combination is a substitute to vlookup.
Match finds the row. Try it
Then, index uses the row within its formula
suggest you look at help index for
MATCH
INDEX

--
Don Guillett
SalesAid Software

quot;Hurtonquot; gt; wrote in message
news
gt; Thanks for the help. I played around with it and got it to return the
gt; values
gt; that i need. My next question is how to get that into a vlookup. I tried
gt; putting the formula in as a reference for the table array but excel
gt; wouldn't
gt; accept the formula.
gt;
gt; Any advice?
gt;
gt; Hurton
gt;
gt; quot;daddylonglegsquot; wrote:
gt;
gt;gt;
gt;gt; Index can give you a cell reference, e.g.
gt;gt;
gt;gt; =INDEX(A1:A20,MATCH(DATE(2006,2,14),A1:A20,0)):B20
gt;gt;
gt;gt; will give you a range from the first cell in A1:A20 with the correct
gt;gt; date to B20, so if 14th february 2006 is first found in A14, this will
gt;gt; give you the range
gt;gt; A14:B20, you could then use this as your range in a VLOOKUP
gt;gt;
gt;gt;
gt;gt; --
gt;gt; daddylonglegs
gt;gt; ------------------------------------------------------------------------
gt;gt; daddylonglegs's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=30486
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=513798
gt;gt;
gt;gt;
I am trying to use a couple of different formulas to return the cell
reference for the table array in a vlookup. The formula will bring back the
letter/number combo that i need but Excel doesn't like it. I tried using the
formula you sent me (modified for the tables i am using) and also a
concatenate of sorts. Standing alone both seemed to bring back the right
thing but excel will not take them as the first part of the table_array. (ie
- formula36)

quot;daddylonglegsquot; wrote:

gt;
gt; What formula did you try?
gt;
gt;
gt; --
gt; daddylonglegs
gt; ------------------------------------------------------------------------
gt; daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
gt; View this thread: www.excelforum.com/showthread...hreadid=513798
gt;
gt;