Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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


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


share|improve this answer

Your Answer


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.