CODEWITHSUNDEEP
excelexcel-formulaformulaformulasarray-formulas
Stacie
Stacie

Reputation: 85

Same Formula responds multiple different ways

I created a formula that doesn't always act the same way. The reason I say that is I've copied and pasted from multiple columns and it works one way and then another. 

{=IF(ISERROR(INDEX('ODG Jobs'!$A$1:$Q$150,SMALL(IF('ODG Jobs'!$C:$C=$B$3,ROW('ODG Jobs'!$C:$C)),ROW('ODG Jobs'!$1:$150)),5)),"",INDEX('ODG Jobs'!$A$1:$Q$150,SMALL(IF('ODG Jobs'!$C:$C=$B$3,ROW('ODG Jobs'!$C:$C)),ROW('ODG Jobs'!$1:$150)),5))}

It works to pull data for a duplicate value, but if I try to drag the formula it won't drag properly or it will mess up the formula. I understand to press shift + ctrl + enter to get the array, but the same formula acts in two different ways is confusing.

Upvotes: 0

Views: 44

Answers (1)

user4039065
user4039065

Reputation:

Try this non-CSE formula which still allows array processing like SUMPRODUCT.

=IFERROR(INDEX('ODG Jobs'!$E:$E, aggregate(15, 6, (row($1:$150)/('ODG Jobs'!$C$1:$C$150=$B$3), row(1:1)), 1), text(,))

Drag down for the second, third, etc matches.

Upvotes: 1

Related Questions