|
Excel2007 vlookup columns, return value from different row
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 |
Excel2007 vlookup columns, return value from different row
Do you want the sum total of *all* the DEC months or just a specific year?
-- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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" wrote: 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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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" wrote: 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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
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" wrote: 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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
Excel2007 vlookup columns, return value from different row
Just drop the month test:
=SUMPRODUCT(--(YEAR('Acct growth'!$D$1:$BL$1)=2010),'Acct growth'!$D$5:$BL$5) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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) -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: 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. -- Biff Microsoft Excel MVP "EG" wrote in message ... 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" wrote: Do you want the sum total of *all* the DEC months or just a specific year? -- Biff Microsoft Excel MVP "EG" wrote in message ... 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 |
| All times are GMT +1. The time now is 09:47 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com