|
Combining a Vlook up with and If then statement
I have a workbook with 2 tabs
The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
A pivot table would do just what you want, with the Acct values in the rows
and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
Select the data source and go to Pivot tables.
In the drop row field select account, in the drop column field select Level.In the drop data items select dollars Regards, Karthik |
Combining a Vlook up with and If then statement
Duke
Unfortunately the Pivot table will not work as I have additional data in the second Tab Worksheet. So I need to match up via a look up by account to populate the different levels. Can you think of any other way? -- Thanks ETC "Duke Carey" wrote: A pivot table would do just what you want, with the Acct values in the rows and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
KarThik
Unfortunately the Pivot table will not work as I have additional data in the second Tab Worksheet. So I need to match up via a look up by account to populate the different levels. Can you think of any other way? -- Thanks ETC "Karthik" wrote: Select the data source and go to Pivot tables. In the drop row field select account, in the drop column field select Level.In the drop data items select dollars Regards, Karthik |
Combining a Vlook up with and If then statement
Sure - create the pivot table as described, then use that as the lookup
table, not your original source data "Sweetetc" wrote: Duke Unfortunately the Pivot table will not work as I have additional data in the second Tab Worksheet. So I need to match up via a look up by account to populate the different levels. Can you think of any other way? -- Thanks ETC "Duke Carey" wrote: A pivot table would do just what you want, with the Acct values in the rows and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
Thanks
I was hoping to do a one step process but that will work -- Thanks ETC "Duke Carey" wrote: Sure - create the pivot table as described, then use that as the lookup table, not your original source data "Sweetetc" wrote: Duke Unfortunately the Pivot table will not work as I have additional data in the second Tab Worksheet. So I need to match up via a look up by account to populate the different levels. Can you think of any other way? -- Thanks ETC "Duke Carey" wrote: A pivot table would do just what you want, with the Acct values in the rows and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
The pivot table would work but if you wanted, you could use the SUMPRODUCT function. In cell B2 of Sheet2! enter in: =SUMPRODUCT(--(Sheet1!$A$1:$A$100=1),--(Sheet1!$B$1:$B$100=Sheet2!$A2),Sheet1!$C$1:$C$100 ) Copy this down your list of account numbers and then accross your level columns. Just change the 1 in [Sheet1!$A$1:$A$100=1] to match the level (2,3 etc..) in your column headings. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538117 |
Combining a Vlook up with and If then statement
Thanks Steve
Would I use that somehow in conjunction with the vlookup in order to match up the accounts? There is more data in Sheet 2 than I have listed. -- Thanks ETC "SteveG" wrote: The pivot table would work but if you wanted, you could use the SUMPRODUCT function. In cell B2 of Sheet2! enter in: =SUMPRODUCT(--(Sheet1!$A$1:$A$100=1),--(Sheet1!$B$1:$B$100=Sheet2!$A2),Sheet1!$C$1:$C$100 ) Copy this down your list of account numbers and then accross your level columns. Just change the 1 in [Sheet1!$A$1:$A$100=1] to match the level (2,3 etc..) in your column headings. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538117 |
Combining a Vlook up with and If then statement
Any idea why my Dollar values when doing the pivot tables are coming up with
a function ie (count, sum product min, max etc.) How do I just get the value -- Thanks ETC "Sweetetc" wrote: Thanks I was hoping to do a one step process but that will work -- Thanks ETC "Duke Carey" wrote: Sure - create the pivot table as described, then use that as the lookup table, not your original source data "Sweetetc" wrote: Duke Unfortunately the Pivot table will not work as I have additional data in the second Tab Worksheet. So I need to match up via a look up by account to populate the different levels. Can you think of any other way? -- Thanks ETC "Duke Carey" wrote: A pivot table would do just what you want, with the Acct values in the rows and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
Not mine
It is defaulting to count dollars, with no opton for value of dollar -- Thanks ETC "Duke Carey" wrote: A pivot table would do just what you want, with the Acct values in the rows and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
No. You don't need a VLOOKUP using this formula. The SUMPRODUCT should do it for you. Broken down the function reads =IF - Sheet1!$A$1:$A$100=1 -AND- IF - Sheet1!$B$1:$B$100=Sheet2!$A2 Then SUM - Sheet1!$C$1:$C$100 So it is looking for your two conditions 1) the level and 2) the account and sums the values in Sheet 1 if both are TRUE. As far as your pivot table, you should use the SUM feature if you are looking for the total dollars for a specific account at a specific level. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538117 |
Combining a Vlook up with and If then statement
Excel is going to force a function - if the combinations of Accts & Levels
are unique it's safe to use SUM, MIN, or MAX "Sweetetc" wrote: Any idea why my Dollar values when doing the pivot tables are coming up with a function ie (count, sum product min, max etc.) How do I just get the value -- Thanks ETC "Sweetetc" wrote: Thanks I was hoping to do a one step process but that will work -- Thanks ETC "Duke Carey" wrote: Sure - create the pivot table as described, then use that as the lookup table, not your original source data "Sweetetc" wrote: Duke Unfortunately the Pivot table will not work as I have additional data in the second Tab Worksheet. So I need to match up via a look up by account to populate the different levels. Can you think of any other way? -- Thanks ETC "Duke Carey" wrote: A pivot table would do just what you want, with the Acct values in the rows and the Levels for the columns. Dollars for data "Sweetetc" wrote: I have a workbook with 2 tabs The 1st tab has the following info A B C Level Acct Dollars 1 3 AAAAA 1,631,276.63 2 2 BBBBB 8,879,065.02 3 1 BBBBB 86.57 4 3 BBBBB 234,986.25 The second tab has the following info: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 2 AAAAA 3 CCCCC I want to combine a vlook up and an if statement to populate the above tab to look like: A B C D Acct Level 1 Level 2 Level 3 1 BBBBB 86.57 8,879,065.02 234,986.25 2 AAAAA 1,631,276.63 3 CCCCC ANY HELP WOULD BE GREATLY APPRECIATED. -- Thanks ETC |
Combining a Vlook up with and If then statement
Thanks I will give it a shot
-- Thanks ETC "SteveG" wrote: No. You don't need a VLOOKUP using this formula. The SUMPRODUCT should do it for you. Broken down the function reads =IF - Sheet1!$A$1:$A$100=1 -AND- IF - Sheet1!$B$1:$B$100=Sheet2!$A2 Then SUM - Sheet1!$C$1:$C$100 So it is looking for your two conditions 1) the level and 2) the account and sums the values in Sheet 1 if both are TRUE. As far as your pivot table, you should use the SUM feature if you are looking for the total dollars for a specific account at a specific level. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538117 |
Combining a Vlook up with and If then statement
It works thanks
-- Thanks ETC "SteveG" wrote: The pivot table would work but if you wanted, you could use the SUMPRODUCT function. In cell B2 of Sheet2! enter in: =SUMPRODUCT(--(Sheet1!$A$1:$A$100=1),--(Sheet1!$B$1:$B$100=Sheet2!$A2),Sheet1!$C$1:$C$100 ) Copy this down your list of account numbers and then accross your level columns. Just change the 1 in [Sheet1!$A$1:$A$100=1] to match the level (2,3 etc..) in your column headings. Regards, Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=538117 |
| All times are GMT +1. The time now is 02:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com