Excel2007 vlookup columns, return value from different row

  • Thread starter Thread starter EG
  • Start date Start date
E

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
 
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
 
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.
 
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
 
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)
 
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.
 
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)
 
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.
 
Just drop the month test:

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