Problem with Index, Small, Array to return multiple values for single look up value,

In the attached file I am having a problem picking up the proper values. It seems to work for the first value but does not copy down correctly.

Jim O

Hi.

Your reference to ROW($C3) in the formulas in row 3 needs to be ROW($C$3).

Regards

.....also, it won't affect the result but if you are using IFERROR, you don't need the first IF, so this will work

=IFERROR(INDEX($D$3:$D$102,SMALL(IF($C$3:$C$102=$H3,ROW($C$3:$C$102)-ROW(C$3)+1),COLUMNS($J3:J3))),"")

To be fair though, DLL, given a choice it's much more efficient to choose the set-up with the IF clause over the IFERROR one.

Perhaps there's not much difference given a dataset of just a hundred rows, but nevertheless in general...

Regards

Seeing as you already have that helper column, you could modify the formula a bit and then use that to pull teh data and eliminate the ARRAY formulas. I used F to make sure we were getting the same results.

F3=C3&COUNTIF(C$3:C3,C3)

Then J3:O3...
=IFERROR(INDEX($D$3:$D$102,MATCH($H3&COLUMN(A$1),$F$3:$F$102,0)),"")

All copied down

You are welcome Depending on the amount of data being used, ARRAY formulas can tend to start slowing things down if used excessively

Originally Posted by XOR LX

.....given a choice it's much more efficient to choose the set-up with the IF clause over the IFERROR one.

OK, that's true. Thanks