Reputation: 851
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
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