Returning a cell reference with a formula?

[Guest]

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

 

[Don Guillett]

Have a look in the help index for VLOOKUP

 

[Pete_UK]

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

 

[daddylonglegs]

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

 

[Guest]

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]

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

 

[Guest]

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]

What formula did you try?

 

[Don Guillett]

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

 

[Guest]

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)

 

[daddylonglegs]

Can you post the formula(s) you tried?

 

[Guest]

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

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):N36,2,0)

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

 

[Guest]

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