CODEWITHSUNDEEP
excelexcel-formulaformulaexcel-2016
TourEiffel
TourEiffel

Reputation: 4434

Excel formula get value just greater than an other

I was wondering how to retrieve the smallest value greater than an other one.

To my knowns :

Using an approximate match,VLOOKUP searches for the value X in column A, finds the largest value less than or equal to X in column A.

What would be the equivalent of VLOOKUP in order to find the smallest value greater than or equal to x

Example : In this table if my value is -0,322

enter image description here

VLOOKUP returns : -0,362

Which Formula would return :-0,317 ?

Upvotes: 0

Views: 1058

Answers (2)

JvdV
JvdV

Reputation: 75960

You could simply use the following array formula:

=MIN(IF(A1:A17>B1,A1:A17,""))

Where B1 would be your the value to compare against.

Upvotes: 2

Harun24hr
Harun24hr

Reputation: 37125

Aggregate() with Index() will help you then. Suppose you have -0.322 in C1 cell then use below formula. See screenshot for better understanding.

=INDEX($A$1:$A$9,AGGREGATE(15,6,ROW($A$1:$A$9)/($A$1:$A$9>$C$1),ROW(1:1)))

enter image description here

Please Note: Comma (,) and dot (.) depends on regional settings. So just replace Dot (.) with comma for your excel.

Upvotes: 2

Related Questions