Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-sheets-queryifs

Google Sheets' query + IFS function

I want to use IFS and Query in google sheet at the same time.

Works Well

=QUERY('PN Orders'!A1:AF,"SELECT C, D where C LIKE '%" & $B$1& "%' and D LIKE '%" & $B$2& "%' LIMIT " &$B$3,1)

above query works well, get results. But every time I combine it with IFS function, it returns nothing.

Doesn't work

=ifs( and($B$1<>"",$B$2<>"")=true, QUERY('PN Orders'!A1:AF,"SELECT C, D where C LIKE '%" & $B$1& "%' and D LIKE '%" & $B$2& "%' LIMIT " &$B$3,1))

Is there something I'm missing here?
Why the query returns nothing when combined with IFS function?

Solution

  • IFS is "array kind of type" formula. what you need in your scenario is simple IF statement:

    =IF((B1<>"")*(B2<>""),
 QUERY('PN Orders'!A1:AF, 
 "select C,D 
  where C like '%"&B1&"%' 
    and D like '%"&B2&"%' 
  limit "&B3, 1), "enter name and phone")

    0

    or perhaps like this:

    =IF((B1<>"")+(B2<>""), 
 QUERY('PN Orders'!A1:AF, 
 "select C,D 
  where C like '%"&B1&"%' 
    and D like '%"&B2&"%' 
  limit "&B3, 1),  "enter name or phone")

    0