+ Reply to Thread
Results 1 to 9 of 9

Returning Multiple Matches from a range

  1. #1
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53

    Returning Multiple Matches from a range

    I am trying to adapt a formula that was used across columns to use across rows. The formula I have no only returns one result which is the first item in column B.

    I want the formula to 1) find a match to the student name on Sheet 1 (Marks) and then return the name of the course they are taking in an adjacent column on sheet 2 (Strugglers) but only if the mark is less than 60. 2) if there is another match to the student name on Sheet 1 it should return the name of the course from that match in the next adjacent column on Sheet 2 with the same mark criteria.

    This is the formula I have so far. The problem I have is that it is only returning the course from the first cell in column B. Help! I am attaching a sample file.

    =IF(COUNTIF(Strugglers!$A$2:$A$25,$A2)>=COLUMNS(B:$B),INDEX(Strugglers!$B$2:$B$25,SMALL(INDEX((Strugglers!$A$2:$A$25=Summary!$A2)*COLUMN(Strugglers!$A$2:$A$25),0),COLUMNS(B:$B)+COUNTIF(Strugglers!$A$2:$A$25,"<>"&Summary!$A2))),"")
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,648
    Hi,

    I've not got time just at the moment, but is sounds like you need an IF...test to test that the mark is less than 60, and if that test is passed a VLOOKUP() function to fetch the name of the course.

    HTH

  3. #3
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Returning Multiple Matches from a range

    DentonHTHS,

    Here you go.

    See the attached workbook "VlookupNth - Returning Multiple Matches from a range - DentonHTHS - SDG.xls" with the Function "VlookupNth".


    Have a great day,
    Stan
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  4. #4
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    Thanks Stan!

    That did seem to work but I had a few problems with it. When I tried to apply it to my real sheet I got a ?NAME error when I expanded the range to cover all my data. The other issue is that I have over a 1000 lines of data in this particular sheet and there is a possibility that it might be expanded to an even larger document. Is there a way to do the formula that is not so calculation heavy? I do like the simpler formula but am wondering if the longer IF test would reduce the calculation load.

    Thanks for all your help so far!

    DentonHTHS

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Returning Multiple Matches from a range

    DentonHTHS,

    There was a Function "VlookupNth" in Module1 in the VBA Editor that you will have to copy to your real workbook, in order for the formulae/Function to work correctly.

    Try that first.


    Have a great day,
    Stan

  6. #6
    Registered User
    Join Date
    09-18-2006
    Posts
    67
    Paste the following into B2:
    =IF(INDEX(Strugglers!$C$2:$C$25,MATCH($A2,Strugglers!$A$2:$A$25,0)+COLUMN()-2)<60,INDEX(Strugglers!$B$2:$B$25,MATCH($A2,Strugglers!$A$2:$A$25,0)+COLUMN()-2),"")

    Then fill it across and down.

    - Clay
    Last edited by clayv; 11-25-2008 at 12:23 PM.

  7. #7
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    I tried your formula, Clay, on my full sheet and ran into a problem. The formula just started filling in the blanks with the next available course on the strugglers sheet in the next blank. So on the first row after getting the first 2 courses right, the formula then just took the 3rd course in the list from the strugglers list.

  8. #8
    Registered User
    Join Date
    09-18-2006
    Posts
    67
    Don't know what the problem is. I've attached a worksheet using the formula and it appears to work fine to me.

    - Clay
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    07-28-2008
    Location
    Moncton
    MS-Off Ver
    2003
    Posts
    53
    I'm not sure either, Clay. The original sheet has almost 800 lines of data so I bumped up all the ranges in the formula to 800. After that, it seemed broken. It's not giving me an error its just doing something funky with the data.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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