SMALL(IF('Carton Info'!$H$8:$H$27=1, ROW('Carton Info'!$H$8:$H$27)-ROW('Carton Info'!$H$8)+1), ROWS(K$8:K8))
This looks for the next available match in sequential order from top of list....
If there is a match to 1 in H8:H27, it returns the next smallest row number according to the K factor determined by ROWS(K$8:K8). ROWS(K$8:K8) equates to 1, as you drag down, it changes to ROWS(K$8:K9) which equates to 2 (for second smallest), etc....
This part: ROW('Carton Info'!$H$8:$H$27) just lists the row numbers in H8:H27, so {8;9;10;11;12;....;27}, but we need to offset this to start at one, so subtracting ROW('Carton Info'!$H$8) from each results in array {0;1;2;3;4.....19} and adding 1 initiates the range at 1 like: {1;2;3;4;5;....20}
so when the condition of 'Carton Info'!$H$8:$H$27=1 results in TRUE, then the ROW('Carton Info'!$H$8:$H$27)-ROW('Carton Info'!$H$8)+1 results in a mix of ROW numbers and FALSEs, and the SMALL chooses next smallest number based on the K factor.
Bookmarks