Google Sheets Query Function With Multiple and Blank (ignored) Criteria

1
votes

I have a sheet (Training) listing information for all employees (over 4,000 rows) and I have a query function on another sheet (Search) that allows me to filter down based on criteria selected by my team. The selections and corresponding cells are:

Company - A3 : First Name - B3 : Last Name - C3 : Email - D3 : PO # - E3 : Class - F3

I need to build a query() that lets me filter using only criteria cells that have information, ignoring completely any criteria that are blank.

The Query() will work using a single criteria or multiple criteria (using "or" and "and") but the information we have won't always be consistent (may have last name but not first name, have email but not company etc.) so I want it to filter using ONLY the data that matches what's been entered by my team and ignore the criteria if the cell (A3:F3) is left blank.

The way the code is written now it includes rows that match ANY of the criteria (I wrote it that way to know I had references correct then was expanding the criteria). I need it to include rows that match only the exact criteria entered.

=Query({Training!A2:AS},"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col19, Col21, Col23, Col25, Col27, Col29, Col31, Col32, Col34, Col37, Col38, Col40, Col42, Col43, Col44, Col45 where Col2 = '"& A3 &"' or Col30= '"& E3 &"' ",0)

To give an example of what I want. If my team enters the following critera into "Search" sheet:

(A3) Company - Metro : (B3) First Name - left blank : (C3) Last Name - left blank : (D3) Email - left blank : (E3) PO # - 123456 : (F3) Class - left blank

My current code will show any row that contains Metro in Col2 or 123456 in Col30. I want it to show rows that only contain BOTH Metro and John in them, ignoring rows that only match 1 criteria and ignoring the other criteria completely since they were left blank.

I've found a few things online that I thought would work, but either they ended up not being what I needed or I'm not versed enough in the function to make them work. Any help with an explanation for teaching purposes would be great.

1
google-sheetsignoregoogle-sheets-querygoogle-query-languagetextjoin
docs.google.com/spreadsheets/d/… – J.Raab
The Query() is on Search A6 with the corresponding criteria entry cells being A3:F3. If you can assist in anyway it'll be greatly appreciated. – J.Raab

1 Answers

0
votes

you can do it like this:

=IFERROR(QUERY({Training!A2:AS},
 "select Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16,Col17,Col19,Col21,Col23,Col25,Col27,Col29,Col31,Col32,Col34,Col37,Col38,Col40,Col42,Col43,Col44,Col45 
  where "&TEXTJOIN(" and ", 1, 
 IF(A3<>"", "Col2 = '"&A3&"'", ), 
 IF(B3<>"", "Col3 = '"&B3&"'", ),  
 IF(C3<>"", "Col4 = '"&C3&"'", ), 
 IF(D3<>"", "Col6 = '"&D3&"'", ),  
 IF(E3<>"", "Col30= '"&E3&"'", ),  
 IF(F3<>"", "Col18= '"&F3&"'", )), 0), "no match")

0