vlookup looking for the next higher value?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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 |
+-------------------------------------------------------------------+
 
=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
 
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)
 
Back
Top