1

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?

Community
  • 1
Liz
  • 11

1 Answers1

2

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)))

barry houdini
  • 11,212