Return values equal to or less than and equal to or greater than lookup value in one-column table-CodePudding

I have a one-column table like this:

I want to find the value that is equal to or immediately less than a given lookup value, and the value that is equal to or immediately greater than a given lookup value. For example, suppose the lookup value is 13. Then the two outputs should be 10 and 15, respectively.

My attempt

I managed to obtain the standard value that is equal to or immediately less than the lookup value, by using the formula

=VLOOKUP(D1, A2:A6, 1, TRUE)

in cell D2, as shown in the following figure, where cell D1 contains the lookup value.

How can I obtain the standard value that is equal to or immediately greater than the lookup value?

CodePudding user response：

If you try to use the LookUp function then you have to reorder the data from highest to lowest to make it work.

However, one way to avoid having the data sorted and still find the upper value and the lower value can be as follows:

D2= SMALL(A2:A6, COUNTIF(A2:A6, "<" & D1))
D3= SMALL(A2:A6, COUNTIF(A2:A6, "<=" & D1)  1)

However, a similar result can also be obtained by combining the SMALL function with INDEX and MATCH.

CodePudding user response：

If you have Excel-365 then you can use XLOOKUP() function with match_mode 1 which indicates exact match or next higher values. Try-

=XLOOKUP(D1,A2:A6,A2:A6,,1)