When I share data with sub-subcontractors, I use the QUERY function to return a subset based on a company name match. I never had any issues in the past, but lately QUERY has not been returning values if they are text, only if they are numbers.

Also, Sheets' "Fill Down" shortcut +D has stopped working (MacBook Air).

Did I mess something up, or did something change?

Example QUERY:

   IMPORTRANGE(spreadsheet_url, range_string),
   "select Col2, Col3, Col4, Col5, Col6, Col7,
      Col8, Col13, Col14, Col15 where Col5
      matches 'company_name'")
  • 1
    Welcome to Web Applications Stack Exchange. The problem is not really with the formula but with the data. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Also consider sharing a publicly editable sample spreadsheet. There is a safe blank sheet maker. Jun 5 at 12:53
  • See @doubleunary's answer vis-a-vis QUERY. With respect to executing the Fill Down command, ensure you are selecting only the target cell OR both the source and target cell. Selecting only the source cell will not work.
    – Blindspots
    Jun 5 at 14:25

1 Answer 1


query have been ignoring data that includes text and not numbers

The query() function only accepts one data type per column. If you have a mix of text strings like abc and numbers like 123 in a column, one or the other type will be returned as nulls.

To get all data from columns that have mixed types, use filter(), like this:

  data, importrange("DOCUMENT", "TAB!A:DK"), 
  filtered, filter(data, search("Company name", choosecols(data, 5))), 
  choosecols(filtered, 2, 3, 4, 5, 6, 7, 8, 13, 14, 15) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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