On this older page, Dan O'Day provides and excellent excel formula to assist with looking up and returning multiple reference values for a given key in separate columns. The formula is:
=INDEX($C$2:$C$8, SMALL(IF($B$12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), COLUMN(A1)))
However, I'm finding that I cannot drag the formula down to other columns because the arrays prevent the formula from recognizing the new cell, specifically after
I can drag across columns but not vertically. Other than going into each new vertical cell and adjusting the cell number in the formula, how can I drag the formula down so it automatically updates with the new cell information?