# Why does google sheets query stop working on 6 decimals (but works on 5)?

I was creating a google sheets query that's pretty simple:

``````query(<range>,"select Col2 where Col1 contains <number with decimals>")
``````

I noticed that it works when the number with decimals contains just 5 decimals, but breaks when it has 6 decimals! I illustrated this problem here:

You can see the above spreadsheet here

note: knowing this limitation, I'm using this method to make the above work:

``````query(<range>,"select Col2 where Col1 contains "&trunc(<cell>,5))
``````

To get the values in sheet March column A on the rows where sheet March column B matches the value in cell A2, use this:

``````=filter(March!A2:A, March!B2:B = A2)
``````

Note that this will get multiple results in the event the value in A2 matches multiple rows in sheet March column B.

The problem with your `query()` formula is that the `contains` operator works with text strings. Numbers lose precision when coerced to text. To get March column A where column B is at its highest, use the `order by & limit` pattern instead:

``````=query(March!A2:B, "select A order by B desc limit 1", 0)
``````
• where is the number I'm searching for you in your query? – abbood Apr 5 at 9:18
• The "highest value" is only an example in my question, it need not be the case. – abbood Apr 5 at 11:29
• Edited the answer to include a `filter()` formula to match rows by any value. – doubleunary Apr 5 at 12:08

There is no value in `March!B:B` that contains `9345.537679`.

In Google Sheets and other spreadsheets apps, the content of the cell is shown in the formula bar. The value shown in the cell, the displayed value, corresponds to the cell content formatted for display purposes only. In the cases of numbers, the value might be rounded, truncated, could include symbols like a currency symbol, etc. But formulas, including QUERY takes the content of cell not the displayed value.

NOTE: TO_TEXT function converts a value to text retaining the number formatting.

The value for `7834681132` is `9345.53767871857` not `9345.537679`.

``````9345.53767871857
^
|
v
9345.537679
``````

If you don't want to use `TRUNC`, among other options, you might set the `March!B:B` number format to display only 5 decimals and use `TO_TEXT`

``````=ArrayFormula(query({March!\$B:\$B,TO_TEXT(March!\$A:\$A)},"select Col2 where Col1 contains 9345.53767"))
``````

• `contains` - A substring match. whole `contains` part is true if part is anywhere within whole. Example: `where name contains 'John'` matches 'John', 'John Adams', 'Long John Silver' but not 'john adams'.