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

Question

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))

Answer 1

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)

Answer 2

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"))

---

From https://developers.google.com/chart/interactive/docs/querylanguage#scalar-functions

• 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'.