PDA

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



Simon Lloyd
03-25-2007, 09:45 AM
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:"&COUNTA(Accounts))),MATCH(Accounts,Accounts,0),""),ROW()-ROW(Accounts)+1))

Regards,
Simon

Simon Lloyd
03-25-2007, 10:27 AM
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)=ROW(INDIRECT("1:"&COUNTA(Accounts))),MATCH(Accounts,Accounts,0),""),ROW()-ROW(Accounts)+1)))

Regards,
Simon