SOLVED

# Question related to Finding Nearest value

Regular Contributor

# Question related to Finding Nearest value

Hello Everyone,

In above screenshot,

I have value of 7.689 & Now I want to find two closest values from the table given (one must be slight higher & one must be slight lower than the original value i have), the values must be 7.838 ( slight higher value) & 7.536 ( slight lower value), how do i find those values from the list have with the help of formula?

I am using Microsoft Office 2019 Home and Student.

Here is a attached file...

5 Replies

# Re: Question related to Finding Nearest value

``````=XLOOKUP(E5,B3:B15,B3:B15,,1)
=XLOOKUP(E5,B3:B15,B3:B15,,-1)``````

# Re: Question related to Finding Nearest value

Sir, i am using Microsoft Office 2019 Home and Student.
So, XLOOKUP function is not there.
Any alternative solution?
best response confirmed by Zan_Hanifee (Regular Contributor)
Solution

# Re: Question related to Finding Nearest value

As variant

``````=AGGREGATE(14, 6, 1/(\$B\$3:\$B\$15<=\$E\$5)*\$B\$3:\$B\$15,1)
=AGGREGATE(15, 6, 1/(\$B\$3:\$B\$15>=\$E\$5)*\$B\$3:\$B\$15,1)``````

# Re: Question related to Finding Nearest value

Thank you so much sir

# Re: Question related to Finding Nearest value

@Zan_Hanifee , you are welcome