2

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Hendry H.
  • 1,482
  • 3
  • 13
  • 27
  • It doesn't make sense to me to use QUERY as argument of IFS. Could you explain briefly why you are trying to do this instead of extenging the where clause of QUERY? – Rubén Jul 12 '19 at 03:16
  • the name or the phone number can be empty. I somehow thinks that the query inside gsheet returns all values from all name, and then filtered it with the phone if the name is empty. So I think just play it safe. Please correct me if I'm wrong. - is there a better query syntax for me ? - is what I'm worrying doesn't make sense ? Thx – Hendry H. Jul 12 '19 at 03:52

2 Answers2

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
1

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166