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):
Formula that doesn't work (AND function):
What am I doing wrong?
Thank you!
Mark
View attachment AND Lookup.xlsx
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