Formula to return unique matches

Hi

I have the following array formula:

PHP Code: 

=INDEX(longextract!$BU$2:INDEX(longextract!$BU:$BU,HideMe!$J$2),SMALL
(IF(longextract!$BS$2:INDEX(longextract!$BS:$BS,HideMe!$J$2)=$A$2,
ROW(longextract!$BS$2:INDEX(longextract!$BS:$BS,HideMe!$J$2))),ROW($A2)-ROW($A$1))-1,0) 


When copied down a column, this gives me all records in longextract!BU where the same row of longextract!BS = A2. Is there a way to alter this formula where it will only return the unique records in BU instead of all of them?

Thank You

Hello Dave,

If you use that exact formula I'd say you'll be a row out because the row number isn't the same as the row number of the range (your INDEX range starts at row 2).

In general I'd use this formula for your situation originally to return all values

=INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10="x",ROW($B$2:$B$10)-ROW($B$2)+1),ROWS(F$2:F2)))

Assuming that A2:A10 is where values are returned from, B2:B10 is the criteria match range and F2 is where the first formula is entered.

To get only distinct values from A2:A10 change to this

=INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10="x",IF(ISNA(MATCH($A$2:$A$10,F$1:F1,0)),ROW($B$2:$B$10)-ROW($B$2)+1)),1))

Note that you are now referencing the cell above the start cell (F1) rather than ROWS(F$2:F2). F1 should either be empty or contain a value that will be different from the entries in A2:A10

For some reason when I used that formula without the -1 on the end, I did end up one row out. I'm not sure if it is because I am using this:
INDEX(longextract!$BU:$BU,HideMe!$J$2)
to find the last row or if it is because I'm putting an artificial first entry into the column that is not on my list.

Anyway, I've modified your formula for my purpose, and now I'm only getting one result, repeated infinitely. Here is how I've modified the formula:

=INDEX(longextract!$BU$2:INDEX(longextract!$BU:$BU,HideMe!$J$2),SMALL(IF(longextract!$BS$2:INDEX(longextract!$BS:$BS,HideMe!$J$2)=$A$2,IF(ISNA(MATCH(longextract!$BU$2:INDEX(longextract!$BU:$BU,HideMe!$J$2),K$1:K1,0)),ROW(longextract!$BS$2:INDEX(longextract!$BS:$BS,HideMe!$J$2))-ROW(longextract!$BS$2)+1)),1))
Confirmed with Ctrl+Shift+Enter.
Does that look correct to you? The K column is empty.

Scratch that, I've worked it out. I didn't read your instructions closely enough
Here is the final formula that worked:

=INDEX(longextract!$BU$2:INDEX(longextract!$BU:$BU,HideMe!$J$2),SMALL(IF(longextract!$BS$2:INDEX(longextract!$BS:$BS,HideMe!$J$2)=$A$2,IF(ISNA(MATCH(longextract!$BU$2:INDEX(longextract!$BU:$BU,HideMe!$J$2),E$2:E2,0)),ROW(longextract!$BS$2:INDEX(longextract!$BS:$BS,HideMe!$J$2))-ROW(longextract!$BS$2)+1)),1))