Hello,
I have a problem with google sheets array formula, one of the queries has no results and I get an error in the output.
Here is the formula, the formula is searching for the month name in each of 3 sheets per input in cell B3, and then searches if there is a change in column vs month before. I get the results but if one of the queries doesn't have a row that satisfies the conditions I get an error.
I tried to wrap queries in iferror, but the error is the same.
Sheet example
Thank you!
I have a problem with google sheets array formula, one of the queries has no results and I get an error in the output.
Here is the formula, the formula is searching for the month name in each of 3 sheets per input in cell B3, and then searches if there is a change in column vs month before. I get the results but if one of the queries doesn't have a row that satisfies the conditions I get an error.
I tried to wrap queries in iferror, but the error is the same.
Code:
={
QUERY(List1!A:AZ,"SELECT A,D,E WHERE ("&SUBSTITUTE(ADDRESS(1, MATCH(B1, List1!1:1, 0)-1, 4), "1", "")&"='Status1' OR "&SUBSTITUTE(ADDRESS(1, MATCH(B1, List1!1:1, 0)-1, 4), "1", "")&"='Status2') AND "&SUBSTITUTE(ADDRESS(1, MATCH(B1, List1!1:1, 0), 4), "1", "")&"='Status3'",1);
QUERY(List2!A:AZ,"SELECT A,D,E WHERE ("&SUBSTITUTE(ADDRESS(1, MATCH(B1, List2!1:1, 0)-1, 4), "1", "")&"='Status1' OR "&SUBSTITUTE(ADDRESS(1, MATCH(B1, List2!1:1, 0)-1, 4), "1", "")&"='Status2') AND "&SUBSTITUTE(ADDRESS(1, MATCH(B1, List2!1:1, 0), 4), "1", "")&"='Status3'",0);
QUERY(List3!A:AZ,"SELECT A,D,E WHERE ("&SUBSTITUTE(ADDRESS(1, MATCH(B1, List3!1:1, 0)-1, 4), "1", "")&"='Status1' OR "&SUBSTITUTE(ADDRESS(1, MATCH(B1, List3!1:1, 0)-1, 4), "1", "")&"='Status2') AND "&SUBSTITUTE(ADDRESS(1, MATCH(B1, List3!1:1, 0), 4), "1", "")&"='Status3'",0)
}
Sheet example
Country Code | Class | Y2023 | Name | City | Jan-2023 | Feb-2023 | Mar-2023 | Apr-2023 |
USA | A | Yes | Nylah | Washington | Status1 | Status1 | Status1 | Status1 |
USA | B | Yes | Alvaro | Seatle | Status1 | Status1 | Status1 | Status1 |
USA | A | Yes | Frida | Denver | Status1 | Status3 | Status3 | Status3 |
USA | C | Yes | Rachael | Washington | Status1 | Status1 | Status1 | Status1 |
USA | C | No | Ronnie | Maryland | Status3 | Status3 | Status3 | Status3 |
USA | A | No | Issac | Washington | Status1 | Status2 | Status2 | Status2 |
USA | C | No | Zack | Seatle | Status2 | Status2 | Status2 | Status2 |
USA | B | No | Natalya | Denver | Status2 | Status2 | Status2 | Status2 |
USA | B | No | Tianna | Maryland | Status2 | Status2 | Status2 | Status2 |
Thank you!