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
  • 2

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
  • 22,693
  • 3
  • 21
  • 25
  • This worked perfectly! Thank you...can you explain _why_ this worked though? – dart852 Jan 02 '18 at 14:47
  • I did in the answer, Column(A:A) returns the column number 1, then when it drags to the right the A:A changes to B:B which returns 2 and so forth. – Scott Craner Jan 02 '18 at 14:49