Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Bill" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Worked great - thank you!
Bill "Max" wrote: 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 Singapore http://savefile.com/projects/236895 xdemechanik --- "Bill" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup
Welcome, Bill. Great to hear that.
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Bill" wrote in message ... Worked great - thank you! Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |