IFS statement with multiple nested QUERY functions

Question

With a fair amount of help from SO folks, I managed to put together about 15 QUERY tabs used to extract necessary data for reporting.

In order to make it tidy, I'd like to enclose all these queries in an IFS statement referring to a Data Validation cell and run the QUERY by simply changing the value from the validation cell.

I managed to get it running with no errors, however, I get a single cell output.

What I am using for this is:

=IFS('Validation'!$A$1 = "Giberish",

              {QUERY('All'!$A$5:$AP, 
                     "SELECT AJ, '" & IDBlah & "', 'Blah' 
                       WHERE AJ IS NOT NULL 
                       LABEL '" & IDBlah & "' 'ID', 
                             'Blah'           'BlahBlahBlah'",1)},

     'Validation'!$A$1 = "GiberishGiberish",

              {QUERY('All'!$A$5:$AP,
                     "SELECT AJ, 'WCaa', 'Blah'
                       WHERE AJ IS NOT NULL
                      LABEL 'WCaa'            'BlahBlah',
                            'Blah'            'BlahBlahBlah'",1);
               QUERY('Upload'!$A$2:$AL,
                     "SELECT A, 'WCaa', 'Blah'
                       WHERE A IS NOT NULL
                      LABEL 'WCaa'              '',
                            'Blah'              ''",0)} )

I tried to enclose it all in an ARRAYFORMULA, but I get the same single cell output, with no error.

What is the correct approach?

Let me know if you need dummy data, but have in mind that I have corporate security policies that restrict me from sharing any sheet to anyone outside of the company.

player0 · Accepted Answer · 2019-02-26 13:44:38Z

1

you will need to use IF instead of IFS like:

=IF(Validation!$A$1 = "Giberish", {QUERY(All!$A$5:$AP, 
                                   "SELECT AJ,'"&IDBlah&"','Blah' 
                                    WHERE AJ IS NOT NULL 
                                    LABEL '"&IDBlah&"''ID','Blah''BlahBlahBlah'",1)},

 IF(Validation!$A$1 = "GiberishGiberish", {QUERY(All!$A$5:$AP,
                                           "SELECT AJ,'WCaa','Blah'
                                            WHERE AJ IS NOT NULL
                                            LABEL 'WCaa''BlahBlah','Blah''BlahBlahBlah'",1);
                                           QUERY(Upload!$A$2:$AL,
                                           "SELECT A, 'WCaa','Blah'
                                            WHERE A IS NOT NULL
                                            LABEL 'WCaa''','Blah'''",0)}, ))

when IFS is in a combination of ARRAYFORMULA it gains a special skill which requires an array to be inputted in order to output an array again.

https://webapps.stackexchange.com/a/124685/186471

answered Feb 26, 2019 at 13:44

player0

131k14 gold badges92 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

I. Я. Newb Over a year ago

Cheers! Very helpful!

Collectives™ on Stack Overflow

IFS statement with multiple nested QUERY functions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related