Listing names with data between values

[ExcelAtTeaching]

Hi. This is my first post. Used Excel on and off for a while and manage to get by through googling functions I need but this one has me stumped.

I'm collating exam results and trying to create lists of names depending on their scores on another sheet. I've managed this so far:

=IFERROR(INDEX('Raw Results'!$A$4:$A$31, SMALL(IF('Raw Results'!$F$4:$F$31<$F$4, ROW('Raw Results'!$F$4:$F$31)-ROW('Raw Results'!$F$4)+1), ROW(1:1))),0)

This is fine to list pupils at the top and bottom ranges (ie greater or less than one score/value).
The problem I have is trying to generate a list that is between two values (ie <F4 but >D4.

I tried using AND within the IF function but to no avail.

=IFERROR(INDEX('Raw Results'!$A$4:$A$31, SMALL(IF(AND('Raw Results'!$F$4:$F$31<$F$4,'Raw Results'!$F$4:$F$31>$D$4), ROW('Raw Results'!$F$4:$F$31)-ROW('Raw Results'!$F$4)+1), ROW(1:1))),0)

Any ideas? Thanks.

 

[Fluff]

Hi & welcome to MrExcel.
Try
SMALL(IF(('Raw Results'!$F$4:$F$31<$F$4)*('Raw Results'!$F$4:$F$31>$D$4),ROW

 

[Marcelo Branco]

To use an AND condition in an array formula try either:

multiply the conditions
IF((Condition1)*(Condition2),...

or nested IFs
IF(Condition1, IF(Condition2,...

M.

 

[ExcelAtTeaching]

Hi & welcome to MrExcel.
Try
SMALL(IF(('Raw Results'!$F$4:$F$31<$F$4)*('Raw Results'!$F$4:$F$31>$D$4),ROW

Thanks so much. works perfectly.

 

[Fluff]

You're welcome & thanks for the feedback