Question related to Finding Nearest value

Regular Contributor

Hello Everyone,

Screenshot (4819).png


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.


Please help..???

Here is a attached file...

5 Replies




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)


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)
Thank you so much sir

@Zan_Hanifee , you are welcome