1

I tried to replicate the top answer provided here by user Dan using the same scenario and formula they provided.

=INDEX($C$2:$C$8,SMALL(IF($B12=$B$2:$B$8,ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1,""),1))

However, when I drag the INDEX array right, I get "Adam" over and over again instead of each unique name.

I found that if I manually change the "k" value at the end of the formula that Dan provided, the formula works and I get the unique names. Example

Cell C12, =INDEX($C$2:$C$8,SMALL(IF($B12=$B$2:$B$8,ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1,""),1))

Cell D12, =INDEX($C$2:$C$8,SMALL(IF($B12=$B$2:$B$8,ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1,""),2))

'Cell E12, =INDEX($C$2:$C$8,SMALL(IF($B12=$B$2:$B$8,ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1,""),3))`

However, this is a rather tedious process and I wish to adapt this formula for a much larger sheet.

My sheet advanced options do have auto increment allowed, as I tested it with simple number series. For example, selecting 1 and dragging right provides a series of numbers increasing by one. I even tried manually setting up C12 and D12, selecting both, and dragging right. However, I simply get repeats of those cells instead of unique names.

Why won't the "k" value increase when I drag it across the sheet? Is there some other issue I'm missing?

dart852
  • 13

1 Answers1

1

Use COLUMN():

=COLUMN(A:A)

will produce 1 and increment to 2 to 3 to 4 to ... as it is dragged to the right.

=INDEX($C$2:$C$8,SMALL(IF($B12=$B$2:$B$8,ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1,""),COLUMN(A:A)))
Scott Craner
  • 23,868