Excel says formula is missing parenthesis

[Sean15]

Hello:

I want formula to return "" for #N/A

=if(IFERROR(LOOKUP(2,1/(COUNTIF($D$2:D10,$B$3:$B$21)=0),$B$3:$B$21),"")

But Excel says formula is missing parenthesis. Could you help please?

Regards,

Sean

 

[Aladin Akyurek]

Try...

=IFERROR(LOOKUP(9.99E+307,1/(COUNTIF($D$2:D10,$B$3:$B$21)=0),$B$3:$B$21),"")

Why is it $D$2:D10?


​

 

[Sean15]

Hello:

I'm trying to Create a list of unique distinct values using the suggestion here: (scroll down to topic Create a list of unique distinct values [Formula]) [url]https://www.get-digital-help.com/2009/03/30/how-to-extract-a-unique-list-and-the-duplicates-in-excel-from-one-column/[/url] Formula starts in D3 then dragged to D21. Regards

 

[Aladin Akyurek]

In D3 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$3:$B$21,SMALL(IF(FREQUENCY(IF(1-($B$3:$B$21=""),MATCH($B$3:$B$21,$B$3:$B$21,0)),
    ROW($B$3:$B$21)-ROW($B$3)+1),ROW($B$3:$B$21)-ROW($B$3)+1),ROWS($D$3:D3))),"")

<strike></strike>

 

[Sean15]

I was hoping to avoid control+shift+enter formula but excellent, thank you very much.

Regards,
Sean

 

[Aladin Akyurek]

I was hoping to avoid control+shift+enter formula but excellent, thank you very much.

Regards,
Sean

You are welcome.

The following set up is fast and does not require array-processing of any kind.

In C2 enter: 0 (mandatory).

In C3 enter and copy down:

=IF($B3="","",IF(ISNA(MATCH($B3,$B$2:B2,0)),LOOKUP(9.99999999999999E+307,$C$2:C2)+1,""))

In D3 enter and copy down:

=IF(ROWS($D$3:D3)>MAX($C$3:$C$23),"",LOOKUP(ROWS($D$3:D3),$C$3:$C$23,$B$3:$B$23))