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))),"")

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

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

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.

Don't know what the problem is. I've attached a worksheet using the formula and it appears to work fine to me.

- Clay

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.