INDEX & MATCH with COUNTA

[Charmwah]

Hi all

I've got a formula which is designed to look at a target value and match all the customers who have that target value. It works perfectly up to a certain point, whereby it returns incorrect values. By this I mean that it returns a match for each customer in the database (when it should only return two matching customers). I've racked my brains checking and re-checking the formula, as well as checking all spellings in the index list and range names including any accidental spaces, the range values, or any information out of order and can't see any. My formula is below:

=IFERROR(OFFSET(GuardDB!$A$3,SMALL(IF(OFFSET(GuardDB!$A$3,1,INDEX(OtherList!$A$2:$A$413,MATCH($K$3,ProdListSung,0)),COUNTA(GuardDB!$A$4:$A$21),1)<>"",ROW(GuardDB!$A$4:$A$21),""),ROW()-ROW($N$3))-3,0),"")

Where:

GuardDB! = Database tab
OtherList! = List tab for index
ProdListSung = Range name
$K$3 = Target value

Does anybody have any ideas? Is there a column limit which i'm hitting? Is it possible that i've got a corrupted file (I've had this happen before but on early versions of Excel, this is Excel 2016)?

Thanks in advance!

 

[Special-K99]

You're using SMALL
Wouldn't this be an array formula?
You haven't mentioned you've entered this as an array formula anywhere.

 

[Charmwah]

Hi Special-K99

It is indeed an array formula and it is entered as such, my apologies if that wasn't made clear. Like I say it works as it should up to a point...

Kind regards