Based on a value in one cell, how do i find its nearest value in a series of numbers in another cell?

Question

In Google Sheets, let's say that in cell B3 I have a (text formatted) series of numbers, separated by commas:

1.1,3.4,5.7,17,42,44,55.8

I need a formula that would take the value in cell A1 and find me the nearest adjacent value from the series of numbers in B3. If the value in A1 is "43", I would like it to return/round off to "44". If it's 42.99, I'd like it to return "42" and so on (as it would normally using a ROUND function).

I came across something like this:

=INDEX(SPLIT(B3, ","), MATCH(MIN(ABS(INDEX(SPLIT(B3, ","),) - A1)), ABS(INDEX(SPLIT(B3, ","),) - A1), 0))

and it seems to me like it should be a fairly straightforward solution, but for some reason it always rounds off to the lower number.

Answer 1

Give a try to

=XLOOKUP(ROUNDDOWN(A1,0),TOCOL(SPLIT(B3,",")),TOCOL(SPLIT(B3,",")),"",1)

Answer 2

In order to get the nearest value to the value in A1, or where it falls exactly mid-way between the two nearest values to take the upper of those two values, I would still use the minimum of the absolute differences then a reverse search to find the first matching value in the list of absolute differences:

=ArrayFormula(let(data,split(B$3,","),
absDiff,abs(data-A1),
minAbsDiff,min(absDiff),
xlookup(minAbsDiff,absDiff,data,"",0,-1)))

Assumes numbers in B3 are in ascending order.