# Returning Multiple Matches from a range

1. ## 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))),"")

2. 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. ## 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

4. 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. ## 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. 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

7. 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. Don't know what the problem is. I've attached a worksheet using the formula and it appears to work fine to me.

- Clay

9. 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.

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

#### 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