Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Fly
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help please - Index and Match "like" values joshtravis Excel Worksheet Functions 1 March 3rd 06 03:26 AM
I need to match multiple columns before returning a value hgopp99 Excel Discussion (Misc queries) 2 January 16th 06 03:46 PM
return multiple corresponding values using INDEX BubbleGum Excel Worksheet Functions 2 January 5th 06 06:43 AM
Returning Multiple Values Based on One Value Nick Excel Worksheet Functions 2 March 31st 05 10:01 PM
Returning Multiple Values Based on One Value Nick Excel Worksheet Functions 5 March 31st 05 12:53 AM


All times are GMT +1. The time now is 09:21 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"