Excel2007 vlookup columns, return value from different row

[EG]

Hello, I am having problem creating this vlookup foluma of a 5 yr plan and
would appreciate some help please. The months 'shift' clumns due project
launch time not firm (ie Dec-09 would move to column E, but the other data
stay put).
D E F G.............
W X .............
1 Oct-09 Nov-09 Dec-09 Jan-10......... Nov-10
Dec-10......
2 Sam 4 6 2 1 5
7
3 Alex 2 1 1 3
3 1
4 Jen 0 2 5 0
1 0
5 Total 6 9 8 4
9 8

Vlook up will find the Dec months and report the 'total', but the Dec months
could be in any column. Thank you very much for any ideas.

GE

 

[T. Valko]

Do you want the sum total of *all* the DEC months or just a specific year?

 

[EG]

No, just need to find any Dec month, and report the 'year end' total. The
Dec month could be in any column. This is what I am using for just the Dec
2010 total, ?? is where I am stuck in pulling the correct colum.

VLOOKUP(40544,'Acct growth'!$D$1:$BL$5,??)

Thank you
EG

 

[T. Valko]

Try this for Dec 2010

Assuming your date headers are true Excel dates.

Array entered** :

=VLOOKUP(40544,'Acct growth'!$D$1:$BL$5,MATCH("Dec10",TEXT('Acct
growth'!$D$1:$BL$1,"mmmyy"),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[EG]

I don't think MATCH function works here. Perhas my question was not clear as
the table was misaligned. Depending which column Dec-10 falls, total to
report could be 6, or 9 or 8 and so on. Vlookup located the column the
Dec-10 is on, but I can't get it to report '9' to be the value. Thank you.

D E F ..... Q..........
1 Nov-10 Dec-10 Jan-11 ... Dec-11....
2 Sam 4 6 2 1
3 Alex 2 1 1 3
4 Jen 0 2 5 0
5 Total 6 9 8 4

 

[T. Valko]

Don't you just need to lookup "total" ?

=VLOOKUP("Total",'Acct growth'!$D$1:$BL$5,MATCH("Dec10",TEXT('Acct
growth'!$D$1:$BL$1,"mmmyy"),0))

You could use something much simpler based on the dates. What are the TRUE
dates of your column headers? Dec-10, what is the TRUE underlying date? I
assume it's just formatted to appear as Dec-10.

Something like this:

=SUMIF('Acct growth'!$D$1:$BL$1,DATE(2010,12,1),'Acct growth'!$D$5:$BL$5)

 

[EG]

wow. the Sumif function worked beautifully. What if I don't have the Dec.
date specific, ie could be any date within Dec. How can I fix that
DATE(2010,12,?) .

THANK YOU.

 

[T. Valko]

To be able to use a generic date we have to use another function:

=SUMPRODUCT(--(MONTH('Acct growth'!$D$1:$BL$1)=12),--(YEAR('Acct
growth'!$D$1:$BL$1)=2010),'Acct growth'!$D$5:$BL$5)

To give it some flexibility, use cells to hold the date criteria:

A1 = 12 (for Dec)
B1 = 2010 (for the year)

=SUMPRODUCT(--(MONTH('Acct growth'!$D$1:$BL$1)=A1),--(YEAR('Acct
growth'!$D$1:$BL$1)=B1),'Acct growth'!$D$5:$BL$5)

 

[EG]

Wonderful, this worked!
How would the formula change if I want to sum by year of row 5 (ie; all the
2010 totals & the 2011 totals etc), instead of just reporting the Dec-10
value. The dates are again month specific, but not day specific.

Thanks for your help.

 

[T. Valko]

Just drop the month test:

=SUMPRODUCT(--(YEAR('Acct growth'!$D$1:$BL$1)=2010),'Acct
growth'!$D$5:$BL$5)