Combining a Vlook up with and If then statement

[Guest]

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.

 

[Guest]

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

 

[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

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

Sure - create the pivot table as described, then use that as the lookup
table, not your original source data

 

[Guest]

Thanks

I was hoping to do a one step process but that will work

 

[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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Not mine
It is defaulting to count dollars, with no opton for value of dollar

 

[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

 

[Guest]

Excel is going to force a function - if the combinations of Accts & Levels
are unique it's safe to use SUM, MIN, or MAX

 

[Guest]

Thanks I will give it a shot

 

[Guest]

It works thanks