Combining a Vlook up with and If then statement

[Sweetetc]

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

[Duke Carey]

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

[Karthik]

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

[Sweetetc]

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

[Sweetetc]

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
>
>

[Duke Carey]

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

[Sweetetc]

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

[SteveG]

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

[Sweetetc]

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
>
>

[Sweetetc]

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

[Sweetetc]

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

[SteveG]

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

[Duke Carey]

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

[Sweetetc]

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
>
>

[Sweetetc]

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
>
>