Lookup with multiple results

[Brian]

On Tab 1 of my spreadsheet, I have the following data

A-Bank Name B-Fund Type C-Amount
D-Maturity Date
1 ABC Bank MMF $1,000
03/05/09
2 BCD Bank MMF $500
03/05/09
3 ABC Bank Comm Paper $2,000
03/10/09
4 ABC Bank Treasury Fund $600
03/05/09

On Tab 2, I'd like to have a formula that will provide me the Fund Type and
Sum of the amounts held by bank based on their maturity date...
For example...on Tab 2....I'd like to have:
ABC Bank 03/05/09 (maturity date) MMF &
Treasury Fund $1,600

I'd like to fill in the bank name and Maturity date fields...but would like
a formula that will list the fund descriptions together "MMF & Treasury
Fund"....as well as add the amounts together.

I can't seem to get an Index or Match function to work...and don't want to
use the Filter function since they are on seperate tabs. Any help would be
appreciated!

 

[Gary''s Student]

The general technique of getting a multi-result lookup without autofiltering
is described in:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

See if you can adapt it.

 

[Brian]

Thanks Gary....unfortunately this is not going to do what I want it to...
It's close....but it will list the matching data in seperate rows....
I'd rather have the resulting data consolidated into one row.
I know I can use the Sumproduct formula to come up with a total that adds
together the information that matches the criteria I'm looking for....but I
also want a formula that will combine text into one cell if it meets the
criteria.
As detailed in my example above....I'd like a formula that will combine text
into one cell with a "A" & "B" format.... Something like using an IF
statement combined with concatenate?
Hope this makes sense....

thanks again
Brian

 

[Herbert Seidenberg]

Excel 2007
Pivot Table, Auxiliary Table, Concatenate
Macro
http://www.mediafire.com/file/rojj4jzkjjw/03_04_09.xlsm

 

[Brian]

Thanks Herb....
Unfortunately I'm stuck using a format that "Sr. Managment has become used
to"...so I can't utilize the pivot table format that you've suggested.
Is there anyway to lookup text in various cells and concatenate them
together into one cell?