Excel - Vlookup based on date specified

Asked By bm

11-Oct-07 04:04 PM

I'm using Excel for accounting functions and have the I have the following
two formulas: "=MONTH(C6)+3" & "=VLOOKUP(A10,'Schol 2007
(Expanded)'!$A:$AE,$C$5)"

What the formulas do is pull financial information from one tab and insert
them into the respective column on another tab all based on the date of input
I specify.

The first formula looks at the date I specify and tells the Vlookup what
column I want returned.  It works fine if I have the month's columns in the
data tab next to eachother, but what formula could i use if there is 1 gap
between months on the data tab?

for instance, January will still start on column 3 (or "C") as specified in
the first formula, but February doesn't start until column 5 (or "E") instead
of column 4 (of "D")

Thanks,

11-Oct-07 04:31 PM

Try this instead of your first formula:

=MONTH(C6)*2+1

will give 3 for Jan, 5 for Feb, 7 for Mar etc.

Hope this helps.

Pete

11-Oct-07 05:04 PM

close...but still not it.  I have 3 columns before the first month starts,
then 1 spacer between each month; so Jan is column D, Feb is Column F, March
is column H and so on.

I could change the amount of columns before the first month starts if need
be...either adding or deleting a row...

I appreciate the help!!!!!

11-Oct-07 05:06 PM

GOT IT!!!! Thanks....=MONTH(C6)*2+2!!!!!!

11-Oct-07 05:13 PM

You did say that January would start at column C, but I see that you
have figured it out.

Pete

11-Oct-07 05:14 PM

You're welcome - glad you found out how to do it.

Pete