Combine two Filter formulas into one (pulling from 2 different sheets)-CodePudding

Currently using this formula={FILTER('Sheet 1'!A3:B,'Sheet 1'!A3:A = "Hired");FILTER('Sheet 2'!A3:B,'Sheet 2'!A3:A = "Hired"}

It works perfectly when there is an instance in both sheets and pulls each row back (just like a single FILTER formula would. However when 1 sheet doesn't have an instance that meets the criteria it breaks giving me this error - "In ARRAY_LITERAL, an Array Literal was missing values for one or more rows."

I have also tried this array formula=arrayformula(FILTER(('Sheet 1'!A3:B,'Sheet 1'!A3:A = "Hired");FILTER('Sheet 2'!A3:B,'Sheet 2'!A3:A = "Hired"))However it isn't pulling back each row one under the other but rather combining the data into one row

e.g rather than

Row 1 - James

Row 2 - John

It shows JamesJohn in Row 1.

CodePudding user response：

use:

={IFERROR(FILTER('Sheet 1'!A3:B,'Sheet 1'!A3:A = "Hired"),{"",""});IFERROR(FILTER('Sheet 2'!A3:B,'Sheet 2'!A3:A = "Hired"),{"",""})}

CodePudding user response：

The iferror() pattern in rockinfreakshow's answer will do the job, but it will generate a blank start row when Sheet1 has not matches. To avoid that, use one filter() that employs { array expressions }, like this:

=filter( 
  { 
    'Sheet 1'!A3:B; 
    'Sheet 2'!A3:B 
  }, 
  { 
    'Sheet 1'!A3:A; 
    'Sheet 2'!A3:A 
  } = "Hired" 
)