VLookup

I am using Excel 2003.

Is there a better way to do what I'm trying to do? Please see my attached spreadsheet where users need to enter quarterly data for the fiscal year.

1st Quarter is Oct, Nov, Dec; 2nd Quarter is Jan, Feb, Mar; 3rd Quarter is Apr, May, Jun; 4th Quarter is Jul, Aug, Sep

So in the months of Jan, Feb, Mar: the user should enter data in column C, and the formulas in columns H and I should use the data entered in column C.
In the months of Apr, May, Jun: the user should enter data in column D, and the formulas in columns H and I should use the data entered in column D.
In the months of Jul, Aug, Sep: the user should enter data in column E, and the formulas in columns H and I should use the data entered in column E.
In the months of Oct, Nov, Dec: the user should enter data in column F, and the formulas in columns H and I should use the data entered in column F.

For example, today is March 15, so the formulas in cells H and I should reference the data entered in column C.

I created a vlookup table in cells O2:P13 where I list the 12 months of the year, the related cells in row 6 and the Quarter names. I got everything working for row 6. I have formulas in cell H6 and I6 and it works fine.

Now, do I need to expand my vlookup table to include the other rows? Am I on the right track or is there a better way to do this?

Thanks,

thanks for writing.

Look at cells P2, P3 and P4. In those cells, i have the formula: =IF(ISBLANK(C6), "",C6).
In cells P5, P6 and P7, I have: =IF(ISBLANK(D6), "",D6)
In cells P8, P9, P10, I have: =IF(ISBLANK(E6), "",E6)
In cells P11, P12, P13, I have: =IF(ISBLANK(F6), "",F6)

So column P takes care of row 6 and that is fine for what I need in the cells in Row 6. But what about the cells in Row 7 and 10? (My actual spreadsheet has about 10 rows of data-in the attached spreadsheet I only show 3 rows as a sample).

So would I have to expand my vlookup table by adding 9 additional columns? For example, one new column in the vlookup table would have the formula: =IF(ISBLANK(C7), "",C7) three times, =IF(ISBLANK(D7), "",D7) three times, =IF(ISBLANK(E7), "",E7) three times and =IF(ISBLANK(F7), "",F7) three times. That would take care of row 7.

I would need to do that 8 more times? So my vlookup table will end up being 12 columns long? (since my actual spreadsheet has about 10 rows of data, I would need one column for each of the 10 rows, one column for the month and one column for the Quarter name)?

Thanks,

OK, first I was trying to answer your question but now I have to rescue you from a terribly complicated solution.

I have completely removed the need from your table in O:P.

Now the headings that give the names of quarters act as a lookup table. I have added the named range "quarters" for this range. Are you familiar with named ranges? Please check Excel Help for details. It is a powerful feature.

Next I added a named formula. This works just like a named range. The formula is

=INT(MONTH(NOW())+2)/3

This formula gives the number of the current quarter. This saves you from having to use a lookup table for every month. The name of this formula is ThisQuarter.

Next, in the existing formulas I replaced your VLOOKUP with

INDEX(C6:F6,,ThisQuarter)

This function returns the cell in the range C6:F6 in the first row (well, there's only one row in that range, so we just leave it empty) and the column corresponding to the quarter. It can be copied down.

Please let me know if you have any questions. This might be a lot to absorb all at once if all of these features are new to you.

wow! this works perfectly! this is so clever! thank you so much. you are amazing.

if it wasn't for you, i would have ended up with a bloated 12 column Vlookup table!

i have been working on this for about one whole day. i was trying to come up with an efficient way to do this. first i started to write IF statements (If(month(today())<=3, ...) but that got too long. so i thought a vlookup table would be more efficient. but your solution blows mine away!

your formula is so clever =INT(MONTH(NOW())+2)/3. and creating named ranges and named formulas is great.

amazing. thank you for taking the time to help me. it is very much appreciated.