How can you add a conditional statement to a =QUERY function

Question

How can I add a conditional statement (similar to an if elseif in .php) to the query listed below so that is will return columns D and E from Sheet2 if column C in the responses sheet == 1st or columns F and G if from Sheet2 if column C in responses sheet == 2nd? It must work within the =QUERY so it only returns column data from the appropriate row (based on the query).

=QUERY(Sheet2!$A$1:K; CONCATENATE("SELECT B, C WHERE A = ", responses!B1), 0)

I looked into IF(OR), but I don't believe that will work because final project will have more than two possibilities (there will actually be eight, 1st through 8th class period).

Here is a link to the Google Spreadsheet I'm working with.

Any suggestions?

Answer 1

The following formula will display the corresponding teachers.

Formula

=ARRAYFORMULA(array_expression)
=VLOOKUP(search_criterion, array, index, sort_order)
=IF(test, then_value, otherwise_value)
=SIGN(number)
=ROW(reference)

=ARRAYFORMULA(                           // ARRAYFORMULA start
   VLOOKUP(                              // VLOOKUP start
     B2:B,                               // search_criterion                 
     DATA!A2:G,                          // array
                                         // index start
     IF(                                 // IF start
       C2:C="1st",                       // test 
         {4,5},                          // then_value
         {6,7}                           // otherwise_value
     ) *                                 // IF end
     SIGN(                               // SIGN start
       ROW(                              // ROW start
         A2:A                            // reference
       )                                 // ROW end
     ),                                  // SIGN end
                                         // index end
     0                                   // sort_order
   )                                     // VLOOKUP end
 )                                       // ARRAYFORMULA end

// to copy / paste
=ARRAYFORMULA(VLOOKUP(B2:B,DATA!A2:G,IF(C2:C="1st",{4,5},{6,7})*SIGN(ROW(A2:A)),0))

Explained

The SIGN and the ROW function are there to meet up with the criteria, set with using an ARRAYFORMULA. It will return an equally long array as setout in the search_condition. The IF function sets the condition for the index, by using the test.

Screenshot

Example

I've copied your file and added my solution to it: teacher references