Returning MULTIPLE values with Index and Match

I am basically writing my own customized Quicken. My problem is I have a Spend Sheet with Date, Amount, Check Number & Category (Numeric). On a summary sheet I have used Index and Match to pull a single Check Number which matches a Category to show summarized Category info. For example Category 1 (Gasoline), I'd like to see Total Amount (I have that working) and ALL check numbers written against that Category.

My problem is I have multiple entries for each Category as my Spend Sheet gets new entries added. With Index & Match I can pull the first check number that matches the specified Category but I'd like to pull ALL check numbers applied against that Category.

For sake of clarity, my Spend Sheet is formatted as follows:
Column A - Date
Column B - Amount
Column C - Check Number
Column D - Category

I feel like I'm missing something simple. Any help is appreciated.

This reply is based on a previous posting (by "Biff") to a problem requiring
a similar solution. I have quickly tested it using your data layout and it
looks OK.

The main formula in your summary sheet is:

=IF(ROWS($1:1)<=$B$1,INDEX(Spend!C$2:C$100,SMALL(IF(Spend!$D$2:$D$100=$A$1+0,ROW(Spend!C$2:C$100)-ROW(Sheet2!C$2)+1,""),ROWS($1:1))),"")

Enter this formula in A4 (for example). This is an array formula and MUST be
entered using the key combination of CTRL,SHIFT,ENTER.

Copy down as required to at least number of cheques expected for category 1.
It will list all cheques for category 1.

In the summary sheet A1 contains the category e.g 1 and B1 contains the
count of number of entries for category 1 in your spend sheet e.g =
Countif(Spend!D:D,A1). A1 & B1 are used in the formula above. You can change
these as required.

Hopefully you can adapt this to your requirements.

HTH

"Fly" wrote:

>
> I am basically writing my own customized Quicken. My problem is I have
> a Spend Sheet with Date, Amount, Check Number & Category (Numeric). On
> a summary sheet I have used Index and Match to pull a single Check
> Number which matches a Category to show summarized Category info. For
> example Category 1 (Gasoline), I'd like to see Total Amount (I have
> that working) and ALL check numbers written against that Category.
>
> My problem is I have multiple entries for each Category as my Spend
> Sheet gets new entries added. With Index & Match I can pull the first
> check number that matches the specified Category but I'd like to pull
> ALL check numbers applied against that Category.
>
> For sake of clarity, my Spend Sheet is formatted as follows:
> Column A - Date
> Column B - Amount
> Column C - Check Number
> Column D - Category
>
> I feel like I'm missing something simple. Any help is appreciated.
>
>
> --
> Fly
> ------------------------------------------------------------------------
> Fly's Profile: http://www.excelforum.com/member.php...o&userid=18101
> View this thread: http://www.excelforum.com/showthread...hreadid=547438
>
>