Find Adjacent Value

[Qiro]

Is there a simple way to find the value in a sorted list that is closest to a sepecific value?

7
2
9
1
6

In the example column above, I have selected the value on row 2, which is 2. I am trying to deciper a way to order the list, then select the next highest/lowest value. In this list, that would be 6 or 1 depending.

I can't use _ROWNUMBER because the values in the column can change, which means the order can change too. Is there a way to ORDERBY to create the list then select the next value up or down in the sequence?

[Suvrutt_Gurjar]

---

@Qiro wrote:

I have selected the value on row 2,

---

Could you mention what exactly the user will do when you say the user has selected value in row 2? Will the user select the row 2? Will the user select the column values from an enum?

[Qiro]

It's referred to by a formula, in effect it allows me to check the value above or below a specific row once all the values are sorted. The user doesn't do anything with the value specifically.

What I'm trying to achieve with the formula is to order the column values into [1, 2, 6, 7, 9] and then for whichever value I choose, get it to return the next or previous value in the list.

[Suvrutt_Gurjar · Accepted solution]

Thank you. 

Please try for nearest lower value an expression of 

INDEX(SORT(Table Name[Column Name], FALSE),

COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & SORT(Table Name[Column Name], FALSE) & " , "),
(" , " & [Column Name] & " , ")
)
),
" , "
)
)-1
)

Please try for nearest higher value, an expression of 

INDEX(SORT(Table Name[Column Name], FALSE),

COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & SORT(Table Name[Column Name], FALSE) & " , "),
(" , " & [Column Name] & " , ")
)
),
" , "
)
)+1
)

The approach is based on the INDEXOF() expression tip shared by @Steve 

INDEXOF() - Google Cloud Community

[Qiro]

This appears to work exactly how I wanted it to, though I do not yet understand why lol I'd never have come up with this myself, thank you.

[Suvrutt_Gurjar]

You are welcome.

The expression, depending on the use case may need a minor adjustment for the highest and lowest value in the entire column.

When the value is highest , the expression will obviously not find one higher value and for lowest value, it will not find one lower value. In such cases, you may need to give some error message or select the highest and lowest value itself again depending on use case.