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

"Hurton" wrote in message
...
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

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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

I was aiming to put this into a vlookup to find the first co ordinate for the
table array if that is possible.

Hurton

"daddylonglegs" wrote:


Why do you need the cell reference, is that your final aim or do you
need it for something else?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

Thanks 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

"daddylonglegs" wrote:


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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

What formula did you try?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

The 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

"Hurton" wrote in message
...
Thanks 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

"daddylonglegs" wrote:


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:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=513798

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
- formula:o36)

"daddylonglegs" wrote:


What formula did you try?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

Can you post the formula(s) you tried?


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

=VLOOKUP(F12,CELL("address",INDEX(L1:L20,MATCH(F12 ,L1:L20,0),1,1))&":N36",2,FALSE)

This returns #Value. When I Showed the Calculation Steps, it was able to
evaluate properly right down the last step. It created
vlookup(Feb-14,"l8:n36",2,false) after calculating out the whole formula.
That was returning the #value but when i typed that exact formula without the
quotation marks in another cell it worked fine.

Thanks for all the help so far by the way

Hurton

"daddylonglegs" wrote:


Can you post the formula(s) you tried?

Are you attempting to find the second match for F12 and return a value
in the corresponding row? This should do that

=VLOOKUP(F12,INDEX(L1:L20,MATCH(F12,L1:L20,0)+1):N 36,2,0)

Note that this will return a value from column M, is that what you
require


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798

Thanks soooo much

This is working beautifully. It has opened a huge realm of things that I
can do with the tables that I work with.

Hurton

"daddylonglegs" wrote:


Are you attempting to find the second match for F12 and return a value
in the corresponding row? This should do that

=VLOOKUP(F12,INDEX(L1:L20,MATCH(F12,L1:L20,0)+1):N 36,2,0)

Note that this will return a value from column M, is that what you
require


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=513798