# Return the closest value from two-dimensional data

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?

• Please make your sheet public if you'd like people to look at it, or post an image
– Nick
May 17 '20 at 19:48

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.

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:

• Please remember that as per site guidelines when an answer addresses your question, accept it and even upvote it so others can benefit as well. May 20 '20 at 17:47
• what is this sorcery `=flatten()``:o where does this function comes from ?
– pjmg
May 25 '20 at 9:14