CODEWITHSUNDEEP
google-sheetsgs-vlookup
DasPete
DasPete

Reputation: 851

Vlookup next largest value

I have some values that I am trying to use vlookup on, the values increment by 4, i.e. (14,18,22,...) I am trying to use vlookup on these values. The problem is that the search key values increment by one, so if the search value is for example 15, it will not be found.

Is there a way to vlookup the next largest value?

Upvotes: 2

Views: 2138

Answers (1)

pnuts
pnuts

Reputation: 59495

Please try: 

=iferror(vlookup(A1,E:E,1,0),indirect("E"&match(A1,E:E)+1))

where A1 is the cell for the search term and E:E is a list sorted ascending of values.

This should not require the steps to be 4 or uniform, but for values outside the quoted range you may wish to insert 0 (assuming only positive numbers are involved) at the top of the list and a very large number at the bottom.

The vlookup part looks for the value in the range E:E in the first column (there is only one for that range) and requires an exact match (0). Where there is no exact match this will return an error that is caught by iferror that instead applies the alternative part of the formula. This looks for an inexact match (E:E is assumed to be sorted ascending) so the inexact match will find the row number of the next lower value. Requirement is for next higher, so add one row and then apply indirect to find the value contained therein, in column E.

Upvotes: 2

Related Questions