Solved: Curing #NUM! when formula finds no match?

[Simon Lloyd]

Hi all i am using a formula for displaying unique values (text) from a list which works fine, but if i copy down further than there are unique items i get #NUM!, does anyone know how to cure it? Here's the formula
=INDEX(Accounts,SMALL(IF(MATCH(Accounts,Accounts,0)=ROW(INDIRECT("1:"&COUNT A(Accounts))),MATCH(Accounts,Accounts,0),""),ROW()-ROW(Accounts)+1))

Regards,
Simon

[Simon Lloyd]

Hi, ive been messing with ISNA, ISERROR and ISERR and come up with this which cures it!
=IF(ISERR(INDEX(Accounts,SMALL(IF(MATCH(Accounts,Accounts,0)=ROW(INDIRECT(" 1:"&COUNTA(Accounts))),MATCH(Accounts,Accounts,0),""),ROW()-ROW(Accounts)+1))),"",INDEX(Accounts,SMALL(IF(MATCH(Accounts,Accounts,0)=RO W(INDIRECT("1:"&COUNTA(Accounts))),MATCH(Accounts,Accounts,0),""),ROW()-ROW(Accounts)+1)))

Regards,
Simon