Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

SMALL(IF($B$12
         ^^^^^

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?

share|improve this question

1 Answer 1

It's the COLUMN function at the end you need to change if you want to copy the formula down rather than across - you can use ROW like this

=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),ROW(A1)))

or it's actually more "robust" to use ROWS function so if first formula is in D3 use this version copied down

=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),ROWS(D$3:D3)))

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.