CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulagoogle-sheets-queryifs
Hendry H.
Hendry H.

Reputation: 1520

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?

Upvotes: 2

Views: 4041

Answers (2)

player0
player0

Reputation: 1

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

Upvotes: 2

Wicket
Wicket

Reputation: 38130

The main problem on your second formula is that is mixing a scalar comparison (single value) with an array of values when both arguments should be of the same type.

Upvotes: 1

Related Questions