3

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?

Improve this question
3
  • 1
    Your question is a bit hard to read. Could you provide an example of which input should generate what output?
    – Vidar S. Ramdal
    Nov 14, 2013 at 14:50
  • Vidar, I'd like to add something like this =if(C3=1st,"responses!D3",IF(C3=2nd,"responses!F3", "Missing Data")) A nested IF statement that will pull celldata from the responses sheet from the row identified by =QUERY above. I'm not sure 'if' that is possible and 'if' it is, I'd like a little help including it in the =QUERY. Thank you!
    – Mr. B
    Nov 14, 2013 at 16:14
  • This will solve all of your three questions, Btw....
    – Jacob Jan Tuinstra
    Nov 16, 2013 at 8:30

1 Answer 1

Reset to default
3

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

enter image description here

Example

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

Improve this answer
1
  • Jacob Jan Tuinstra, Thank you! That worked PERFECTLY!
    – Mr. B
    Nov 19, 2013 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.