Lookup nearest value

Panorama X
1

Is it possible to look up the nearest value rather than an exact match?
(As you can do in a spreadsheet)

2

I don’t know exactly what spreadsheets do when looking up the nearest value, but I wrote a procedure to find the nearest value to the selected value in a numeric field named SomeValues. I thought it would be simpler, and this seems a little clunky to me. And I am surprised nobody has posted a solution. I assumed you want the closest value not equal to the current value whether it is larger or smaller than the current value.

/* Finds the nearest value to the current value of field SomeValues.  Will find two values, one above and one below, if they are both the closest to the current value. */

let lvtestval=SomeValues  //Assigns value to search near.

let lvarr0=arraybuild(",","",{SomeValues},{SomeValues≠lvtestval})  
  //Creates an array of values to be searched

let lvarr=arraybuild(",","",{abs(SomeValues-lvtestval)},{SomeValues≠lvtestval}) 
  //Creates an array of absolute values of deltas for each element of the search array

let lvmindelta=val(array(arraynumericsort(lvarr,","),1,","))
   //Finds the lowest delta

let lvarr2=arraydeduplicate(arraystrip(arrayfilter(lvarr0,",",{?(abs(val(import())-lvtestval)=lvmindelta,import(),"")}),","),",")
   /*Filters the original array to select only the nearest values, 
     both above and below;  then strips empty array elements 
     and delete duplicates.*/

alertsheet lvarr2
3

You might want to check out the table( function which returns the closest match. Seems odd that this is not included as an option in the Relational Workshop wizard.

4

Could you provide more detail about this? I’ve never heard about a “nearest lookup” function in a spreadsheet, and this has never been requested before (assuming you don’t mean the table( function, which Gary mentioned). How does it work? What sort of applications would it be used for? What is the feature called (so I can Google it and find out more information)? If I know more about it, I could consider adding a feature like this in the future.

5

This is an example of what I mean (just for illustrative purposes only)
Say I have a database called “Cars” in which I have fields such as make, model, year etc. I also have a field for fuel consumption - say in mpg. However I want to express the fuel consumption in l/100km.
So I set up another database with two fields - ‘mpg’ and ‘lkm’ that shows the conversion from mpg to l/100km. This database has records starting at 10mpg going through to 40mpg - in 2 mpg increments ie.10,12,14,16 etc.
Back in ‘Cars’ I use the lookup function to find the conversion for 17mpg. But that record does not exist in ‘mpg’ so the it defaults to 0. In a spreadsheet it will generally use either 16 or 18mpg - ie the nearest lookup.
Hope this clarifies.

6

For you example, you don’t need a lookup. 1 mpg=~0.425 lpk.

7

I think you are talking about a spreadsheet function like VLOOKUP. You can set up a parameter for approximate or exact matches.

8

The example was for illustrative purposes only.
‘Table’ function seems to do what I am looking for.
Thanks for the help

9

I did a Google search and it appears that the table( function does exactly what Excel’s VLOOKUP function does when the range_lookup parameter is true. So neither Excel nor Panorama really find the “nearest” value, but rather the nearest value at or below the specified value.