## How to find the nearest greater value and closest lower val

### How to find the nearest greater value and closest lower val

Hi All,

Please help me to get formulas to find the nearest greater/equal value and the closest lower value from the list.

Attached the sheet for reference.

I want to input any random number in 'D1' and it should scan column 'A' with numbers and print the values on 'G2' and 'G3'. In this example I used 22 as input and expect 23 in G2. Please help.

With love...
Attachments
Test.ods
example
OpenOffice 4.1.6 on Windows 10
sudheeshm

Posts: 1
Joined: Sat Dec 14, 2019 4:33 pm

### Re: How to find the nearest greater value and closest lower

Welcome to the Forums.
You could get the smaller of the two numbers by using the VLOOKUP function, with a formula like
Code: Select all   Expand viewCollapse view
`=VLOOKUP(D1;A2:A19;1)`

Please note that this will return the exact match if the number you enter in D1 is in the range A2:A19

I'm having a harder time getting something that returns the greater value...but I'll keep at it... in the mean time, there's a start.

This formula
Code: Select all   Expand viewCollapse view
`=INDIRECT(ADDRESS(RANK(D4;A2:A19;1)+2;1))`
seems to work for returning the higher of the two numbers you were wanting
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.

RusselB
Moderator

Posts: 6100
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

### Re: How to find the nearest greater value and closest lower

If it is allowed to have a copy of the list in the reverse order in column B, then you can use
Code: Select all   Expand viewCollapse view
`=INDEX(B2:B19;MATCH(D1;B2:B19;-1))`

and
Code: Select all   Expand viewCollapse view
`=INDEX(A2:A19;MATCH(D1;A2:A19;1))`
Attachments
Test_fjcc.ods
Windows 10 and Linux Mint, since 2017
FJCC
Moderator

Posts: 7742
Joined: Sat Nov 08, 2008 8:08 pm

### Re: How to find the nearest greater value and closest lower

You may also want to study the attached example.
Attachments
aoo100380NearestUpDownFromList_1.ods
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München

Lupp
Volunteer

Posts: 2911
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany