Index...small....row

[Jan Kalop]

=INDEX(F$44:F$79,SMALL(IF($S$44:$S$79=$S$42,ROW($S$44:$S$79)-ROW($S$44)+1),ROWS($A$1:A1)))


It is possible change above formula in section =$S$42 to options biger and smaller at the same time (for sample >$S$42-3 and <$S$42+1)




Both below are obviously not working:


=INDEX(F$44:F$79,SMALL(IF($S$44:$S$79=AND(>$S$42-3,<$S$42+1),ROW($S$44:$S$79)-ROW($S$44)+1),ROWS($A$1:A1)))
or
=INDEX(F$44:F$79,SMALL(IF($S$44:$S$79=AND($S$44:$S$79>$S$42-3,$S$44:$S$79<$S$42+1),ROW($S$44:$S$79)-ROW($S$44)+1),ROWS($A$1:A1)))

 

[Fluff]

Untested but try
=INDEX(F$44:F$79,SMALL(IF(($S$44:$S$79>$S$42-3)*($S$44:$S79<$S$42+1),ROW($S$44:$S$79)-ROW($S$44)+1),ROWS($A$1:A1)))

 

[Eric W]

2 slightly different ways to do that:

=INDEX(F$44:F$79,SMALL(IF($S$44:$S$79>$S$42-3,IF($E$44:$S$79<$S$42+1,ROW($S$44:$S$79)-ROW($S$44)+1)),ROWS($A$1:A1)))

=INDEX(F$44:F$79,SMALL(IF(($S$44:$S$79>$S$42-3)*($S$44:$S$79<$S$42+1),ROW($S$44:$S$79)-ROW($S$44)+1),ROWS($A$1:A1)))

both with Control+Shift+Enter. Using AND or OR in array formulas doesn't usually work, since it works on more of the array than you want.

 

[Jan Kalop]

Faaaaaaaaaaaaaantastic............thank you very much. I love yours help

 

[Fluff]

Glad we could help & thanks for the feedback