Google Sheets Query cannot find some numbers

Question

I'm trying to use a QUERY function to get a maximum value of a range where value in another column equals to a specified value with

=QUERY(L$4:M,"select max(M) where L="&K1&" label max(M)''")

I tried K1=99.75, 109.725 or 114.7125 every values returns its M values until L goes above 114.7125 it returns N/A

I tried query in L80:L100 with 119.7 which exists in row 88 also returns N/A

I tried making up values with 110.7225 111.7200 112.7175 113.7150 114.7125 115.7100 116.7075 117.7050 118.7025 119.7000 it stops working with 115.7100

I even tried =QUERY(L$4:M,"select M where L='119.7'") it returns ± (the column header) and when I change to =QUERY(L5:M... it returns N/A

Here are L4:M data. All are actual values, not a function result. :

    | L            | M
----+--------------+-----------
  4 | Balance      |  ±
  5 |  99.75000000 |  -6023.86
  6 |  94.76250000 |  -1776.75
  7 |  89.77500000 |   2468.11
  8 |  85.60030690 |   6062.98
  9 |  84.78750000 |   6762.90
 10 |  79.80000000 |  11107.56
 11 |  74.81250000 |  15501.55
 12 |  77.11415433 |  13444.10
 13 |  79.80000000 |  11029.79
 14 |  75.81250000 |  14543.18
 15 |  74.81250000 |  15423.28
 16 |  75.43038050 |  14873.37
 17 |  79.80000000 |  10984.45
 18 |  84.78750000 |   6570.56
 19 |  89.77500000 |   2136.72
 20 |  99.75000000 |  -6789.91
 21 | 104.07461484 | -10461.51
 22 | 104.73750000 | -11024.30
 23 |  99.75000000 |  -6904.62
 24 | 104.73750000 | -11144.00
 25 |  99.75000000 |  -7099.14
 26 |  89.77500000 |   1190.09
 27 |  84.78750000 |   5434.45
 28 |  85.83562716 |   4543.36
 29 |  80.84812716 |   8787.72
 30 |  79.85062716 |   9670.51
 31 |  79.80000000 |   9714.86
 32 |  84.78750000 |   5002.17
 33 |  89.77500000 |    201.70
 34 |  94.76250000 |  -3783.31
 35 |  99.75000000 |  -7696.00
 36 |  94.76250000 |  -3950.39
 37 |  99.75000000 |  -7835.65
 38 | 104.73750000 |  11611.35
 39 |  99.75000000 |  -8264.74
 40 |  95.00454252 |  -4890.72
 41 |  94.76250000 |  -4718.63
 42 |  89.77500000 |  -1120.15
 43 |  90.74750000 |  -1838.83
 44 |  89.75000000 |  -1105.87
 45 |  84.76250000 |   2539.99
 46 |  79.77500000 |   6205.65
 47 |  89.77500000 |  -1284.35
 48 |  84.78750000 |   2162.01
 49 |  79.80000000 |   5708.12
 50 |  77.06182232 |   7696.04
 51 |  75.34427695 |   8942.98
 52 |  74.81250000 |   9329.05
 53 |  79.80000000 |   5543.54
 54 |  84.78750000 |   1828.35
 55 |  87.68533294 |   -234.88
 56 |  87.84530696 |   -348.78
 57 |  89.77500000 |  -1722.70
 58 |  94.76250000 |  -5208.96
 59 |  99.75000000 |  -8645.35
 60 | 104.73750000 |  11587.98
 61 | 109.72500000 |  14555.04
 62 | 104.73750000 |  11906.68
 63 |  99.75000000 |  -9205.95
 64 | 101.52622830 |  10092.29
 65 | 104.51872830 |  11585.55
 66 | 109.72500000 |  14183.48
 67 |  99.75000000 |  -9186.00
 68 |  89.77500000 |  -3689.77
 69 |  95.90606936 |  -6871.79
 70 |  99.75000000 |  -8866.79
 71 |  89.77500000 |  -3370.56
 72 |  79.80000000 |   2375.04
 73 |  69.82500000 |   8370.02
 74 |  74.81250000 |   5307.69
 75 |  79.80000000 |   2320.18
 76 |  74.81250000 |   5068.29
 77 |  69.82500000 |   8267.77
 78 |  74.81250000 |   4947.69
 79 |  79.80000000 |   1511.30
 80 |  89.77500000 |  -4363.98
 81 |  94.76250000 |  -6982.42
 82 |  99.75000000 |  -9725.05
 83 | 104.73750000 | -12093.61
 84 | 109.68750000 | -14269.09
 85 | 114.71250000 | -16416.54
 86 | 119.68750000 | -18540.99
 87 | 124.68750000 | -20641.54
 88 | 119.70000000 | -18890.88
 89 | 124.68750000 | -20781.14
 90 | 129.67500000 | -22471.90
 91 | 134.66250000 | -24112.79
 92 | 129.67500000 | -22711.30
 93 | 124.68750000 | -21210.06
 94 | 119.70000000 | -19634.01
 95 | 128.07469046 | -22064.35
 96 | 129.67500000 | -22531.64
 97 | 134.66250000 | -23978.76
 98 | 139.65003446 | -25444.60
 99 | 134.66253446 | -24092.99
100 | 139.65000000 | -25536.86

Am I doing anything wrong? Or is there any limitations on the QUERY function?

Answer 1

... it stops working with 115.7100

The formula

=QUERY(L$4:M,"select max(M) where L="&K1&" label max(M)''")

returns N/A because the L column doesn't contain 115.7100.

I even tried =QUERY(L$4:M,"select M where L='119.7'") it returns ± (the column header)

The above formula returns only ± because '119.7' is a text value and L column contains numbers.

Answer 2

After I stumbled on this Stack Overflow thread I think I know the cause of my problem.

It should be about the data type of the cells. As the most of data is numbers, some of them are texts.

The data was converted values from formula results. The conversion was made in scripts. Sorry for not posted in the original question.

I think some of them are text because of my manual edits after the code runs.

All I have to do is change data formats for those cells. And to make sure, I did include the function TEXT(L5, "0.00000000") in data cells' and query formula.

So my query is now

=QUERY(L$4:M,"select max(M) where L="&TEXT(K1, "0.00000000")&" label max(M)''")

And I did change to use order-limit query as @Kos's comment. Now my final query is like this

=QUERY(L$4:M;"select M where L='"&TEXT(K1, "0.00000000")&"' order by M desc limit 1 label M ''")

I don't know which one is better. But I prefer the former one.