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. -- Fly ------------------------------------------------------------------------ Fly's Profile: http://www.excelforum.com/member.php...o&userid=18101 View this thread: http://www.excelforum.com/showthread...hreadid=547438 |
Returning MULTIPLE values with Index and Match
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(I F(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 |
All times are GMT +1. The time now is 08:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com