• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

What's causing this #NA error ? am using VSTACK and FILTER

shili12

Member
Apologies for not posting xlsx workbook as should be the norm, but i suppose the formula thru observation can be resolved, if not i will try to eliminate sensitive data and upload xlsx workbook
column A column B column C column D
1692621530594.png

the formula used in column b1 is
=VSTACK(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1,'1stfebtill31stdec2021'!$E$2:$E$134137)),"??"),FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1,'1stjantill31stdec2020'!$E$2:$E$84468)),"??"))

worksheets are named as '1stfebtill31stdec2021' and '1stjantill31stdec2020',
$A1 is kmfr111T

in column b2, ?? is ok but why are rest of those reflecting #N/A and how do i resolve it ? :rolleyes:
 
Last edited:
If either FILTER function returns no results, they return a single ?? value, not an array of those values. VSTACK returns #N/A for any missing values. You should wrap that in IFERROR to replace them.
 
doesnt work out still
=VSTACK(IFERROR(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1, '1stfebtill31stdec2021'!$E$2:$E$134137))),""),IFERROR(FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1, '1stjantill31stdec2020'!$E$2:$E$84468)),""),""))

1692623602304.png
 
or even if i place back => "??"
=VSTACK(IFERROR(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1, '1stfebtill31stdec2021'!$E$2:$E$134137)),"??"),""),IFERROR(FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1, '1stjantill31stdec2020'!$E$2:$E$84468)),"??"),""))
1692623850978.png
 
You need to wrap the VSTACK in IFERROR, not have the IFERROR inside the VSTACK (since the FILTER functions are not returning any errors).

For example:
=IFERROR(VSTACK(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1,'1stfebtill31stdec2021'!$E$2:$E$134137)),"??"),FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1,'1stjantill31stdec2020'!$E$2:$E$84468)),"??")),"??")
 
Thanks, i got it now:-
=IFERROR(VSTACK(FILTER('1stfebtill31stdec2021'!$A$2:$G$134137,ISNUMBER(SEARCH($A1, '1stfebtill31stdec2021'!$E$2:$E$134137)),"??"),FILTER('1stjantill31stdec2020'!$A$2:$G$84468,ISNUMBER(SEARCH($A1, '1stjantill31stdec2020'!$E$2:$E$84468)),"??")),"??")

1692625887258.png
 
Back
Top