PDA

View Full Version : amending Array MATCH formula to cope with blanks?



Simon Lloyd
03-25-2007, 10:58 AM
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)=ROW(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
03-25-2007, 11:25 AM
B1: = Accounts
B2: =IF(ISERROR(MATCH(0,COUNTIF(B$1:B1,Accounts&""),0)),"", INDEX(IF(ISBLANK(Accounts),"",Accounts),MATCH(0,COUNTIF(B$1:B1,Accounts&""),0))) copy B2 down, and it is an array formula

Bob Phillips
03-25-2007, 11:27 AM
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
03-25-2007, 01:46 PM
Hi Bob,
Your post looks OK to me.

Bob Phillips
03-25-2007, 02:33 PM
Hi Malcolm, Look at num 2, that is totally messed.