3

If any of the queries in an array formula do not have actual data to query in the range they are hitting they return #VALUE! and mousing over the array formula reveals an error. If I take those queries and wrap them in an IFERROR I get the same results.

If I take what I wrapped in an IFERROR and split it out into its own cell to validate the query it results in displaying the error clause which in this case is a 0.

Here is a link to an example sheet.

Sheet1 has sample data.
Sheet2 is intentionally blank to simulate the issue described above.
Sheet3 has three queries on it in various states. The top two are the array formulas I am attempting to work with. The bottom Query is the IFERROR split out into its own cell to show that the query does in fact work when separated from the rest of the sort(arrayformula(etc)).

Community
  • 1
  • 1
Overlordchin
  • 185
  • 3
  • 3
  • 13

1 Answers1

3

Try combining both ranges (from both sheets) inside 1 query instead of using 2 queries, and wrap an IFERROR() around that single query:

=ARRAYFORMULA(IFERROR(QUERY({Sheet1!A1:I500; sheet2!A1:I500}, "Select * where Col7='no'", 0), 0))

See if that works for you ?

JPV
  • 26,499
  • 4
  • 33
  • 48
  • That works in the case where the query is searching for the same thing. I have a similar case in a different sheet where I am doing the same thing but multiplying the results by the value in a different cell. `code QUERY('sheet1'!A3:G51, "Select B where A contains '"&$F$10&"'")*P34)+(QUERY('sheet1'!A3:G51, "Select C where A contains '"&$F$10&"'")*P35)` this use case has the exact same issue but seems like it cant be solved by your solution. Though your solution totally works in the use case I presented originally. – Overlordchin Apr 24 '15 at 13:59
  • Can you add that issue to the sheet you shared ? – JPV Apr 24 '15 at 14:06
  • yeah it will just take a bit to add some sample data – Overlordchin Apr 24 '15 at 14:08
  • Ok I added some sample data to another sheet in the doc. For whatever reason the formula is breaking claiming one of the values that is a number is not a number and I am not sure why. I chopped a small part out to run that by itself and it has the same problem. Ill keep poking at it but this at least lets you see the full query and what it is doing even in its current state. – Overlordchin Apr 24 '15 at 14:52
  • Can you show what the output of the formula should be give the current data ? – JPV Apr 24 '15 at 15:09
  • I think in the actual sheet the value generated is roughly 120 – Overlordchin Apr 24 '15 at 15:10
  • See if this works: =ArrayFormula(min(mmult(Vlookup(F10, data!A3:I51, {2, 3, 4, 6},0), {60; 40; 0.25; 1})-Vlookup(F10, data!A3:I51, 9,0),M33)) – JPV Apr 24 '15 at 15:43
  • I had no idea I could structure it like that. that worked perfectly. – Overlordchin Apr 24 '15 at 17:01
  • best answer bro – myworldbox May 03 '21 at 16:18