Combining INDEX, SMALL and ROW functions... incorrectly?

zredbaron

New member
Joined
May 22, 2018
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Professional Plus 2013
Hi.

I would like to dynamically display the top 5 matches in a table that fall within a given date range.

Clearly I'm doing something wrong... the problem I am having is that I can display top 5 above a data range, and top 5 below a date range but I'm not able to provide a formula that does both at once.

Formulas that work (no AND function):
Code:
{=IFERROR(INDEX($A$2:$A$25,SMALL(IF([B]$F$3>=$B$2:$B$25[/B],ROW($A$2:$A$25)-ROW($A$2)+1),1)),"")}

{=IFERROR(INDEX($A$2:$A$25,SMALL(IF([B]$E$3<=$C$2:$C$25[/B],ROW($A$2:$A$25)-ROW($A$2)+1),1)),"")}

Formula that doesn't work (AND function):
Code:
{=IFERROR(INDEX($A$2:$A$25,SMALL(IF([B]AND($E$3<=$C$2:$C$25,$F$3>=$B$2:$B$25[/B]),ROW($A$2:$A$25)-ROW($A$2)+1),1)),"")}

What am I doing wrong?

Thank you!
Mark

View attachment AND Lookup.xlsx
 
The AND() function doesn't work with Array Formulas... instead use nested IF() or mulitply the arrays together to yield arrays of 1's and 0's (i.e. Trues and Falses).

e.g Try:

=IFERROR(INDEX($A$2:$A$25,SMALL(IF(($E$3<=$C$2:$C$25)*($F$3>=$B$2:$B$25),ROW($B$2:$B$25)-ROW($A$2)+1),E25)),"")

confirmed with CTRL+SHIFT+ENTER and copied down.

Note: You can replace the k parameter of the SMALL function with a reference to E25 since you have a consecutively numbered sequence. This allows you to copy down without manual intervention of each formula
 
Perfect! Thank you good sir or lady, that works perfectly.

I also noticed you cleaned up the back half of my formula as well. Thanks for two lessons at once!

Brilliant. :)
 
Last edited:
Back
Top