I’m used the Filter to fill in data from sheet to another. Base of the information in Salesperson_Table6[Unit Size] which is single cell. When Some people open it on their pc this function is not working and it as _xlfn._xlws. add to the start of it. Been try a couple different work around but not seem to be working. Can anyone help? See my function below.
=FILTER('50% Offer'!B5:B33,'50% Offer'!A5:A33=Salesperson_Table6[Unit Size],"Not found")
I try this
=IFERROR(INDEX('50% Offer'!B5:B33,AGGREGATE(15,7,ROW('50% Offer'!A5:A33)/('50% Offer'!A5:A33=C12),ROW($ZZ1))),"")
But it return the wrong value, It seem to pick a value 3 row down then one I need need.
>Solution :
Try using this:
=IFERROR(INDEX('50% Offer'!$B$5:$B$33,
AGGREGATE(15,7,(ROW('50% Offer'!$A$5:$A$33)-ROW('50% Offer'!$A$5)+1)/
('50% Offer'!$A$5:$A$33=C12),ROW($ZZ1))),"")
The array needs to return a range of 1,2,3,etc so it refer the matching row values.
This part of the formula does that
(ROW('50% Offer'!$A$5:$A$33)-ROW('50% Offer'!$A$5)+1)