Forum Discussion
Need help creating a dynamic list on excel 2016
=IFERROR(INDEX($C$2:$C$11,SMALL(IF($B$2:$B$11=$F$5,ROW($A$2:$A$11)-1),ROW(E1))),"")Maybe with this formula. Enter the formula with ctrl+shift+enter as you don't work with Office365 or 2021.
If to avoid array formula and make it bit dynamic
=IFERROR( INDEX( $C$2:INDEX(C:C, COUNTA(C:C) ),
AGGREGATE(15, 6, 1/
( $B$2:INDEX(B:B, COUNTA(B:B) ) =$F$5) *
$A$2:INDEX(A:A, COUNTA(A:A) ), ROW()-ROW($H$4) ) ), "" )- Thanks a ton! Would you be able to explain briefly how this formula works?
We use dynamic ranges here, e.g.
$C$2:INDEX(C:C, COUNTA(C:C) )
is equivalent of $C$2:$C$11 for your sample. If you add more rows there is no need to re-write the formula.
AGGREGATE() works natively with the arrays, thus you don't need to use Ctrl+Shift+Enter. With it we take Nth (4th parameter ROW()-ROW($H$4) ) smallest (first parameter 15) value from the col1 ignoring errors (second parameter 6). Errors are generated if col2 is not equal to input, when
1/( $B$2:INDEX(B:B, COUNTA(B:B) ) =$F$5) generates division on zero error, otherwise returns 1. That is multiplied on col1 index. Instead of col1 we may use ROW() on range.
Thus function returns 2 in first cell, 5 in next, etc. We wrap by INDEX to return value from col3 for that position and all together is wrapped by IFERROR() to return empty string if position is not found.
