shortlisting of values with 1 criteria

[nielsb]

Hi there,

I'm in search for a formula which returns a shortlist of unique values that meet one criteria. I've illustrated the problem in attached sheet. Columns B-D cover the data (source), out of which column C-D comprise the criteria.
The envisaged formula would then be in column G-H and return the source-values only once.

Much appreciated if someone can help me out with this..

Kind regards,
Niels

[CARACALLA]

G3=IFERROR(INDEX($B$3:$B$16,SMALL(IF(FREQUENCY(IF(C$3:C$16="x",MATCH($B$3:$B$16,$B$3:$B$16,0)),ROW($B$3:$B$16)-ROW($B$3)+1),ROW($B$3:$B$16)-ROW($B$3)+1),ROWS($B$3:B3))),"")

Control+shift+enter

copy across and down

[nielsb]

1111111111

[nielsb]

Dear Caracala,

thanks for your attempt. However the formula doesn't work. Possibly to do with the function 'frequency' not appearing in my list of formulae (MS Excel 2016). Is there a way I can upgrade my excel to capture 'Frequency', or perhaps a workaround / alternative formula?

[AliGW]

FREQUENCY is available in Excel 2010, so it's not that. What error message do you get?

[nielsb]

Appologies Caracala,
your formula DOES work.. I just got the wrong translation for the 'frequency' function. In dutch it's 'interval'..

Many thanks!!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[CARACALLA]

You are welcome