SOLVED

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

@Zan_Hanifee 

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

 

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

@Zan_Hanifee 

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