Lookup with multiple results
 

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!

Lookup with multiple results
 

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

http://office.microsoft.com/en-us/ex...260381033.aspx

See if you can adapt it.
--
Gary''s Student - gsnu200836


"Brian" wrote:

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!

Lookup with multiple results
 

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

"Gary''s Student" wrote:

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

http://office.microsoft.com/en-us/ex...260381033.aspx

See if you can adapt it.
--
Gary''s Student - gsnu200836


"Brian" wrote:

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!

Lookup with multiple results
 

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

Lookup with multiple results
 

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?

"Herbert Seidenberg" wrote:

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