Vlookup adding more than one instance

[ExcelUser777]

Good Evening

All - I have searched this group high and low for an answer and I can't
seem to grasp anything to create what I'm after.. Though I totally
appreciate all the information

Here it goes...

I have 2 sheets in Excel. The first sheet in Excel lists a master
financial statement description list of the balance sheet, a balance
sheet if you will

First Sheet - Master Balance Sheet

cash
accounts receivable
prepaid assets

I assigned a numerical value for each
Cash = 1
Accounts Receivable = 2
prepaid assets = 3
All the way from "1" = Cash to "41" = Total Liabilites and Owners
Equity

Second Sheet - Balance Sheet has different descriptions but in the end
only 41 different numbers will be picked up from sheet 2

This sheet contains a financial statement with

Cash = "1" 40,000
Money Market = "1" 20,000
Accounts Receivable = "2" 20,000
Prepaid Assets = "3" 10,000

All the way down to "41" = Total Liabilites and Owners Equity

I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000
because VLOOKUP will only pick up one instance...

How do I get Excel to pick up all instances of 1 all instances of 2
etc... without using SUMIF?
I tried sumif, but that didn't seem to work right ...



Thanks,
ExcelUser777

 

[Biff]

Hi!

So, what do you want to do, list all amounts that coresspond to a certain
number category?

Lookup all instances of 1 and return:

40,000
20,000

Biff

 

[ExcelUser777]

Wow what a quick response
Hi Biff

Yes I would like to be able to do what you mentioned.
On the second sheet I might have instances of 3 threes etc. 4 fours
etc. etc...
Vlookup is limited for this type of issue?

My goal is to set up a Macro to handle the description grouping.. but I
am creating this manual first and then automate later..


I actually just got the sumif to work for me, but I read that Sumif can
be faulty when you are linking files and I use alot of linked files.
I noticed alot of posting using arrays, but these solutions seemed to
be if people wanted to multiply figures in one column 1 by column 2


Thanks,
ExcelUser777

 

[Biff]

Ok, I'm still not 100% sure of what you want to do but have a look at this
sample file:

http://s63.yousendit.com/d.aspx?id=19D5KICKLSAK00K1V3DOANEYN6

Is that what you had in mind?

The formula that returns the category amounts is an array formula. It must
be copied to enough cells so that all the coressponding amounts are
returned. Since the number of amounts vary by category you need to copy the
formula to enough cells that will cover the maximum number of amounts for
ANY category.

Biff

 

[wdjsxj]

That's great.

“Biffâ€ç¼–写：

 

[Biff]

Ooops!

Caught a bug!

Change this portion of the array formula:

=IF(ROWS($1:1)<=B$2

To:

=IF(AND(ROWS($1:1)<=B$2,B$2<>"")

This modification will account for cell B2 being empty. (which currently
causes an error)

Biff

 

[ExcelUser777]

Biff,

This is excellent...when I finally get it working

The Array doesn't seem to work when you choose 5 from the drop down
list.


Thanks,
ExcelUser777

 

[ExcelUser777]

Biff,

Thanks a bunch
I love how I can choose the number from your Excel Spreadsheet.

What I want to have happen is to show all instances of "1" summed up
into one cell, all instances of "2" summed up into one cell, what you
did is great

Sumif works...I just don't know if that is the best way do it....

 

[Biff]

The Array doesn't seem to work when you choose 5 from the drop down

list.

It works for me.

Biff

 

[Biff]

You're welcome. Thanks for the feedback!

Biff