cheesy_goodness
New Member
- Joined
- May 19, 2014
- Messages
- 24
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!
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!