+ Reply to Thread
Results 1 to 4 of 4

Return Multiple Matches into Corresponding Table

  1. #1
    Registered User
    Join Date
    05-26-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    5

    Return Multiple Matches into Corresponding Table

    Hi there,

    I have a table with a number of system options and their associated characteristics (including price).
    The last column of my table is a data validation column, with the option to include (options are 'Yes', 'No').

    I need to return the system options that have been selected as 'Yes' into a different table, and include their associated cost.
    I want to avoid Macros and I've been look at VLOOKUP, INDEX-MATCH and so on but can't figure out how to return multiple matches.

    I have a feeling its quite a simple solution. I've attached an example of the problem - thanks in advance for your help!
    Kind regards,

    P

    :-)
    Attached Files Attached Files
    Last edited by QuantumP; 07-28-2016 at 05:03 AM.

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    2016
    Posts
    3,836

    Re: Return Multiple Matches into Corresponding Table

    Try

    H3=IFERROR(INDEX(Table1[System],SMALL(IF(Table1[Include?]="Yes",ROW(Table1[Include?])-ROW($F$3)),ROWS($H$3:H3))),"") with Control+Shift+Enter and drag down!!
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Return Multiple Matches into Corresponding Table

    Try these...

    This array formula** entered in H3:

    =IFERROR(INDEX(B:B,SMALL(IF(F$3:F$10="Yes",ROW(F$3:F$10)),ROWS(H$3:H3))),"")

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    This formula entered in I3:

    =IF(H3="","",VLOOKUP(H3,B$3:E$10,4,0))

    Select H3:I3 and copy down until you get blanks.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    05-26-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    5

    Re: Return Multiple Matches into Corresponding Table

    Wonderful, thank you very much!!!

+ 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. Return defined number of matches in table
    By inq80 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-05-2015, 06:43 PM
  2. Search a table with multiple search inputs and return all matches
    By JDI in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-16-2014, 12:23 PM
  3. Replies: 2
    Last Post: 03-03-2014, 10:03 PM
  4. Return multiple columns with vlookup when there are multiple matches
    By elapo21 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-06-2013, 07:11 PM
  5. Look up and return multiple matches
    By ShoshanaM in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-05-2013, 05:59 PM
  6. find and return multiple matches in a table, then sum and report total
    By cyclops755 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-08-2013, 06:57 PM
  7. Excel 2007 : Return Multiple Matches
    By GaidenFocus in forum Excel General
    Replies: 8
    Last Post: 11-23-2010, 12:37 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