Return the closest value from two-dimensional data

Question

I have an input value and a table of two-dimensional data in Google Sheets. I would like to output the value from the table that is closest to the input value, either without going over the input value or without going below the input value. See this sheet as an example, with the two-dimensional table in the second worksheet.

The only other solutions I have found are for one-dimensional data. In other words, they find the closest value in a single column rather than covering multiple columns (see, for example, here and here).
However, I haven't been able to come up with a way to adapt those to multiple columns.

How can this be done?

Answer 1

Here are two formulas to do this :

Closest value at or below

=array_constrain(
    SORT(
        filter(
            UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE('Z table'!C2:L41&"🐥"),"🐥")))),
            UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE('Z table'!C2:L41&"🐥"),"🐥"))))<B3
        )
    ,1,false)
,1,1)

Closest value at or above

=array_constrain(SORT(filter(UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE('Z table'!C2:L41&"🐥"),"🐥")))),UNIQUE(ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE('Z table'!C2:L41&"🐥"),"🐥"))))>B3),1,true),1,1)

Methodology :

Regroup all the data in a one-dimensionnal array, then filter the values above or below the input value. (Need unique values of a range in google sheets)

Then sort them with =sort() and keep only the first value with =array_constrain().

Working example here.

Answer 2

There is no single formula that could return results for both the Closest at or below as well as the Closest at or above. We can have though one simple query for each.

Closest value for equal or lower

=QUERY({flatten('Z table'!C2:L41)},"select Col1 where Col1 <= "&H1&" order by Col1 desc limit 1",0)

Closest value for equal or greater

=QUERY({flatten('Z table'!C2:L41)},"select Col1 where Col1 >= "&H1&" order by Col1 limit 1",0)

Functions used:

• QUERY
• SORT
• FLATTEN(undocumented)