Is there any way to compare a fixed value to a list of values, and return the closest match in the list?

[Ryan_S]

Is there any way to compare a fixed value to a list of values, and return the closest match in the list?
It’s all numeric

I’ve got a fixed list returned from a select operation on another table (Could be 1 result, could be 100 results) and a single value in my current table. Need the closest match from the list. Been at this problem for about 2hrs now

[Marc_Dillon]

Is there any way that you can add another column to the other table (where the list of values comes from), that calculates ABS( [value] - fixed value )? Then you can use

LOOKUP( MIN(other table[newcolumn]) , other table, new column, value )

[Ryan_S]

I see where you’re going with that, but I’m not sure how I could integrate a solution like that

The table it’s pulling the values from are all on separate rows, and the value it’s being compared to changes for each ‘active’ record… It’s a disaster the way i’ve got things laid out to make up for the shortcomings of “no code”…lol

Wish there was a way to add some minimally needed code

[Marc_Dillon]

I’ve been wracking my brain trying to think of a clever solution, but got nada.

You can trigger a Google Apps Script to do the work. Search here in the forums about it.

[Ryan_S]

I’ll look into that in a bit… Still trying to figure out if I can implement your original idea

Thanks for trying

[Suvrutt_Gurjar]

Could you please confirm if the following understanding is correct?

If the SELECT() list from the other table returns say {4, 10, 18, 28, 35, 50}

If the fixed value in the current table is 12, the the expression should return 10, if the fixed value 2 it should return 4, if the fixed value is 23, it should return one out of 18 or 28, if the fixed value is 26 , it should return 28, if the fixed value is 55 it should return 50?

[Steve · Accepted solution]

This will give you closest row:

ANY(
  ORDERBY(
    FILTER(
      "table",
      select-expr
    ),
    ABS([_THISROW].[value] - [value])
  )
)

replacing table, select-expr and value with the appropriate table name, row selection expression, and value column name, respectively.

[Suvrutt_Gurjar]

Wow! Very compact @Steve.

[Ryan_S]

Should change the name of Any to First, as in returns the First value in the list, also there’s little documentation on orderby being able to do calculations on the fly

Elegant solution, love it!
Thanks Steve!!

[Marc_Dillon]

Yah that’s cool. I wouldn’t have expected ORDERBY (or similar expression) to work on an on-the-fly calculated value.

[Steve]

It took me a long time to understand ORDERBY() worked that way.