CODEWITHSUNDEEP
excelexcel-formulavlookup
Masroor
Masroor

Reputation: 908

How to make VLOOKUP() match with the next greater value?

Please consider the table below:

enter image description here

If we write VLOOKUP(12,A1:B6,2,TRUE) we will get 2 as a result since the lookup value will match with 10 in the first column of the array and return the corresponding value in the second column.

What I am looking for is somewhat different. I want to find the value corresponding to the next greater value. For example, when my lookup value of 12, it will now match with 15 in the first column and then return 3 from the second column.

How do I do this preferably with VLOOKUP function?

Upvotes: 1

Views: 7445

Answers (2)

basic
basic

Reputation: 11968

If you do not have O365 available then you can use the INDEX/MATCH functions entered as array formula:

=INDEX($B$1:$B$6,MATCH(TRUE,$A$1:$A$6>12,0))

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

If you have Excel O365 with the FILTER function, and if your lookup value is in D1, you can use:

=MIN(FILTER(B1:B5,A1:A5>=D1))

If the entries in column B are text, and not sorted numeric, you can use:

=INDEX(FILTER(B1:B5,A1:A5>=D1),1)

Upvotes: 1

Related Questions