1

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 at 19:48
  • Done. Sorry about that; thought I had already done so. – Vincent May 18 at 5:09
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.

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

closest value from two-dimensional data in Google Sheets without VLOOKUP

Functions used:

| improve this answer | |
  • 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. – marikamitsos May 20 at 17:47
  • what is this sorcery `=flatten()``:o where does this function comes from ? – pjmg May 25 at 9:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.