amending Array MATCH formula to cope with blanks?

[Simon Lloyd]

Hi all as per a previous post i have this formula which i fixed with ISERR: {=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)))}
The formula works great but if the list has a gap in it all of the other values it returns when copied down show#N/A, does anyone know how to cure this?

Regards,
SImon

[Bob Phillips]

B1: = Accounts
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,Accounts&""),0)),"", INDEX(IF(ISBLANK(Accounts),"",Accounts),MATCH(0,COUNTIF(B$1:B1,Ac counts&""),0))) copy B2 down, and it is an array formula

[Bob Phillips]

Sorry my account is well and truly f*\%!d up, and so I am just going to stop responding, it has become too much like hard work.

[mdmackillop]

Hi Bob,
Your post looks OK to me.

[Bob Phillips]

Hi Malcolm, Look at num 2, that is totally messed.