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:
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!
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:
Code:
=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!
