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

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

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.

Solution

Give a try to

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