vlookup looking for the next higher value?

D

deugniet418

I would like to look-up a value in a table. and in case of not an exact match
select the next higher value.
Vlookup rounds of downwards.
 
B

Bryan Hessey

I'm sure someone has a better method, but if you insert a column within
your lookup table (or on the right of it) you can use that as an offset
to pick up the next higher value.

See attached, the working line is


=IF(VLOOKUP(A21,$A$1:$C$9,1,2)=A21,VLOOKUP(A21,$A$1:$C$9,1,2),OFFSET($C$1,VLOOKUP(A21,$A$1:$C$9,2,2),0))


where column B is the increment column.

Hope this helps.

I would like to look-up a value in a table. and in case of not an exact
match
select the next higher value.
Vlookup rounds of downwards.
Click to expand...


+-------------------------------------------------------------------+
|Filename: RowPlusOne.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4095 |
+-------------------------------------------------------------------+
 
N

Niek Otten

=IF(VLOOKUP(C1,A1:B6,1)=C1,VLOOKUP(C1,A1:B6,2),INDEX(B1:B6,MATCH(C1,A1:A6)+1))
Table in A1:B6, search value in C1
 
D

Dave Peterson

One more...

=INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,1)+ISERROR(MATCH(A1,Sheet2!A:A,0)))

The table is in sheet2, columns A:B (I brought back column B, but matched on
column A)
 
Top