Index/Match Array Formula Help

[cheesy_goodness]

Hello all!
Every time I hit a brick wall with my spreadsheets I take a look around these forums and almost always find a great solution!

Unfortunately I haven't been able to find an answer to my current problem...

Essentially it boils down to needing multiple values returned based on multiple arguments. I'm trying to avoid macros since the spreadsheet will be used by people not entirely familiar with excel. I've used variations of following array formula in the past with great with great success:

{=INDEX("Range for value being returned",SMALL(IF(C1="Range for value being tested",ROW("Range for value being tested")-MIN(ROW("Range for value being tested"))+1,"",Row($A1))}.

With my current spreadsheet, however, I need a value returned based on several conditions, namely values that fall within a certain range. I've tried the following and didn't have any results:

{=INDEX("Range for value being returned",SMALL(IF(AND(B1>="Range for value being tested",C1<="Range for value being tested"),ROW("Range for value being tested")-MIN(ROW("Range for value being tested"))+1,"",Row($A1))}

I end up getting a #VALUE! Error. My thought was making an IF AND statement would be what I need, but I haven't had any luck.

Any thoughts on the matter are greatly appreciated.
Thanks!

 

[JoeMo]

Maybe (untested):

=INDEX("Range for value being returned",SMALL(IF((B1>="Range for value  being tested")*(C1<="Range  for value being tested"),ROW("Range for value being  tested")-MIN(ROW("Range for  value being tested"))+1,"",Row($A1))

 

[cheesy_goodness]

Thanks for the reply Joe.

I tried your suggestion and it's returning values now, but it's not returning the correct values.

This is what I have now:
{=INDEX('Trial Balance'!$M$12:$M$3045,SMALL(IF(($C$3>='Trial Balance'!$B$12:$B$3045)*($E$3<='Trial Balance'!$B$12:$B$3045),ROW('Trial Balance'!$B$12:$B$3045)-MIN(ROW('Trial Balance'!$B$12:$B$3045))+1,""),ROW($A1)))}

Where C3 is the beginning range of account numbers and E3 is the last number in the range.

It's returning mostly 0 values, but occasionally it will return a value based on an incorrect account number. I don't see any pattern in what's being returned.