1. ## lookup (?) (2000)

I have a list of daily raw data going back to 1/1/1965. I would like a formula that tells me the cell reference that is the first day of the 30th year prior to the current year. For example, in column I have listed the dates of the raw data, beginning with 1/1/1965 through today, 11/14/2004. In column B I have the raw numerical data. I would like a formula that "looks" at the dates in column A and tells me the cell reference of 1/1/1974. Any help?

2. ## Re: lookup (?) (2000)

="A"&MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)

To get the "raw numerical data" corresponding to this date, you could use:

=VLOOKUP(DATE(YEAR(TODAY())-30,1,1),A:B,2,0)

Steve

3. ## Re: lookup (?) (2000)

Thanks, Steve, works swell! One slight twist: what if I have multiple (say 6 columns) of data, and want to get, say, the raw numerical data from column E that corresponds to the same date as per your first formula?

Thanks again.

4. ## Re: lookup (?) (2000)

Change the range to read all 6 columns, then grab the fifth one (col E):

<pre>=VLOOKUP(DATE(YEAR(TODAY())-30,1,1),A:F,5,0)</pre>

Steve

5. ## Re: lookup (?) (2000)

Yep-works great. Thanks Steve.

6. ## Re: lookup (?) (2000)

Just got another twist to include (very frustrating-why can't they give me the whole problem at first!):Is there a way to use the reference cell derived by your first formula as a reference or an "anchor" in, say, a sum formula. For example, assume your first reference formula returns A3290. In another remote cell on the same or a different worksheet, how can I use that result indirectly in a sum formula, say, that would sum the next 180 cells column B, so that the formula would in essence be "=sum(b3290:b3470)"?

7. ## Re: lookup (?) (2000)

=SUM(INDIRECT("B"&MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)&":B"&MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)+180))

Here is another that doesn't do 2 lookups:
=SUM(OFFSET(B1,MATCH(DATE(YEAR(TODAY())-30,1,1),A:A,0)-1,0,180,1))

Steve

8. ## Re: lookup (?) (2000)

Thanks for your quick response. Unfortunately, when I tried the first formula, I got a zero, and with the second, a zero, but with a circular as well...

9. ## Re: lookup (?) (2000)

Where did you put the formula. It worked fine for me in col C1. Are the values in col b, text?

Is the formula in the range of interest or do any of the values in this range, reference the formula cell? (this would cause the circ reference)

Steve

10. ## Re: lookup (?) (2000)

Aha-I put the formula on another spreadsheet, but where the reference was to "A:A", I used the cells on the correct worksheet, and, no, all of the cell values are hard data that have been input (i. e., numerical data, not formulas or references).

I will try putting the formula in c1.

11. ## Re: lookup (?) (2000)

If you are refereing to things in another sheet, you need to also add the sheet reference to the items.

Steve

I did.

13. ## Re: lookup (?) (2000)

Could you explain how you have it setup, what you want, and what you modified the formulas to?

The indirect formula will also need the explicit sheet name in it if it is different than the sheet with the formula

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•