Lookup

[Bill]

When using VLOOKUP, the lookup value is compared to the first column. If
there is no exact match, it returns the closest match that is LOWER than the
lookup value. Is there a way that I can specify it returns the closest match
that is HIGHER than the lookup value?

Part of my worksheet has a series of engine sizes in the left hand column.
According to user input of their vehicle's engine size, the lookup should
return a value accordingly. My current list of sizes is entered as:
(up to)
2000, 2500, 3000, etc.

Naturally with engines, they range greatly in size and if my engine is (for
example) 2200cc, I want to return a value from the 2000 to 2500 row (simply
listed in my sheet as '2500') This would require Vlookup to return the next
higher value.

Thanks in advance for any help.

 

[Max]

Assume the lookup table is in cols A & B, from row1 down
col A = 2000,2500,3000,etc (capacity values in ascending order)
col B = corresponding ref values

Assume in D2 down are listed the capacity values to be looked up,
eg: 2200, 2800, 2500, etc

In E2
=IF(ISNUMBER(MATCH(D2,$A:$A,0)),VLOOKUP(D2,$A:$B,2),INDEX($B:$B,MATCH(D2,$A:$A)+1))
Copy down

 

[Bill]

Worked great - thank you!

Bill

Assume the lookup table is in cols A & B, from row1 down
col A = 2000,2500,3000,etc (capacity values in ascending order)
col B = corresponding ref values

Assume in D2 down are listed the capacity values to be looked up,
eg: 2200, 2800, 2500, etc

In E2:
=IF(ISNUMBER(MATCH(D2,$A:$A,0)),VLOOKUP(D2,$A:$B,2),INDEX($B:$B,MATCH(D2,$A:$A)+1))
Copy down

 

[Max]

Welcome, Bill. Great to hear that.