CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Rich
Rich

Reputation: 5

Index-Small formula keeps repeating

The excel formula that I'm using appears to be repeating the same results upon condition of only one value on my conditional formatting drop down list. In this case, it repeats the results only for "Pacira Biosciences". How can I fix it to stop repeating the same results just like the other items on the drop down list?

The formula is this:

=IFERROR(INDEX(RAW!$D$2:$D$279,SMALL(IF(RAW!$B$2:$B$279=$C$5,ROW(RAW!$D$2:$D$279)-1,10^10),ROWS($A$1:A1))),"")

Screenshot of Problem

Excel File can be seen/downloaded here on Google Drive: https://drive.google.com/file/d/1UYw1ZX941yr2gVY0DOBd_YEGI1d9jmPG/view?usp=sharing

Upvotes: 0

Views: 123

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use this array formula:

=IFERROR(INDEX(RAW!$D$2:$D$279,SMALL(IF(((RAW!$B$2:$B$279=$C$5)*(IFERROR(MATCH(RAW!$D$2:$D$279,IF(RAW!$B$2:$B$279=$C$5,RAW!$D$2:$D$279),0)=ROW(RAW!$D$2:$D$279)-1,FALSE))),(ROW(RAW!$B$2:$B$279)-1)),ROW(1:1))),"")

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode

If in the future you upgrade to OFFICE 365 then it gets much simpler:

=UNIQUE(FILTER(RAW!$D$2:$D$279,RAW!$B$2:$B$279=$C$5))

Upvotes: 3

Related Questions