Comparing a row agains each element of a table

[Luis_DfG]

Hello,

I have two tables: (1) Product and (2) Storage

In my Product table I have a virtual column that automatically calculates the product suggested storage position based on a calculation (SuggestedPosition). For example: 40 (from 0 to 100).

In the Storage table I have a Position column. For example: 10, 30, 60, 100, 120, etc.

I'm looking for a way to identify which would be the closest suggested storage by comparing: (a) Product[SuggestedPosition] vs (b) Each row in the Storage[Position] column and get the minimum/closest one.

For example:

ABS(40-10)=30, ABS(40-30)=10 (winner) , ABS(40-60)=20

So far I've tried:

ANY(SELECT(Storage[id],
(
MIN(SELECT(ABS(Storage[Position]-[SuggestedPosition]),FALSE))
),
TRUE))

but with no luck.

Thanks for any tip!

[Joseph_Seddik]

IF(
  ABS(
    [Position] -  
    MAX( SELECT(Storage[Position], [Position] <= [suggestedPosition]) )
  ) < 
  ABS(
    [Position] -  
    MIN( SELECT(Storage[Position], [Position] >  [suggestedPosition]) )
  ), 
  MAX( SELECT(Storage[Position], [Position] <= [suggestedPosition]) ),
  MIN( SELECT(Storage[Position], [Position] >  [suggestedPosition]) )
)

[Luis_DfG]

Thanks Joseph!

I have a small question.

Is it possible to update/modify the elements of a LIST?

For example: LIST(4, 3, 2) => LIST(4+1, 3+1, 2+1) => LIST(5, 4, 3)?

[Joseph_Seddik]

Yes, putting the list elements in rows. 

[Luis_DfG]

Like literally putting them in rows inside the spreadsheet? I'm wondering  if its possible with a expression.

[Joseph_Seddik · Accepted solution]

You can either:

• Yes, literally as rows in the sheet, or
• Using recursive (looping) actions, or
• Through an Apps Script