+ Reply to Thread
Results 1 to 5 of 5

Excel Formula to extract multiple matches from Column matching criteria

  1. #1
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    Unhappy Excel Formula to extract multiple matches from Column matching criteria

    Hi,

    I have a pivot table with 4 columns of data: a concatenation of salesperson and account, Salesperson, Customer Name and Sales Amount.

    Off to the side of the pivot table i have a lookup table that i am using for my dynamic chart that i want to show all the customer names and sales for just the particular salesperson selected. I've tried multiple formulas and I can't get it to pull back the 1st match. Instead it pulls back somewhere in the middle and then pulls back customer names that don't even fit the criteria. I thought this would be pretty simple.

    I am attaching the workbook and you can see the other formula's i've tried in the text box and not one worked properly.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Excel Formula to extract multiple matches from Column matching criteria

    Try this instead: =IFERROR(INDEX(Customer,SMALL(IF(Sales_Person=$H$1,ROW(Sales_Person)-4),ROWS($1:1))),"")

    Entered as an array formula with CTR:+SHIFT+ENTER
    Spread the love, add to the Rep

    "None of us are as smart as all of us."

  3. #3
    Registered User
    Join Date
    02-20-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    74

    Re: Excel Formula to extract multiple matches from Column matching criteria

    THANK YOU! what was i doing wrong?

  4. #4
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: Excel Formula to extract multiple matches from Column matching criteria

    Try with
    In G3
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    In H3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  5. #5
    Forum Expert
    Join Date
    09-11-2014
    Location
    Washington, DC
    MS-Off Ver
    2016
    Posts
    1,907

    Re: Excel Formula to extract multiple matches from Column matching criteria

    Quote Originally Posted by krunk View Post
    THANK YOU! what was i doing wrong?
    You were very close.

    =IFERROR(INDEX(Customer,SMALL(IF(Sales_Person=$H$1,ROW(Sales_Person)),ROW()-2),1),"")

    The ROW(Sales_Person) was your flaw. This returns the row in which a TRUE value is returned from your IF logic. However, since you start your data table on the 5th row, you need to subtract 4 rows to return the correct ordinal position for INDEX to work correctly.

    To clarify, right now your first correct logic match occurs on row 8 (first instance of Autry, Jamie). Your formula, written as is, is returning the row value of 8 to pull from INDEX. But Autry, Jamie occurs as the 4th record in your data table, so what you actually want to tell INDEX is to pull the value from the 4th entry, not row. That is why you subtract 4.

    Clear as mud?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 12
    Last Post: 03-13-2016, 01:20 PM
  2. Formula(s) to extract string that matches certain criteria from a text
    By Turtler in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 01-27-2014, 05:18 AM
  3. [SOLVED] Count text in one column Matching single or multiple Criteria from Other Column Excel 2003
    By Jose Macieira in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2013, 06:55 AM
  4. [SOLVED] two-array matching formula with multiple matches
    By ilikeexcel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2013, 02:19 PM
  5. Find Some Matches Data In column With Criteria And Extract In ROW
    By a-a_m_ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-05-2012, 07:03 AM
  6. Replies: 2
    Last Post: 07-19-2011, 04:08 PM
  7. Extract multiple records matching criteria from list
    By William DeLeo in forum Excel Formulas & Functions
    Replies: 56
    Last Post: 09-06-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1