Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get VLOOKUP to look for the next greater value
I have a worksheet with a bunch of pull down menus where data can be selected
and then inputed into a formula. I then want to take the formula and automatically have a part# selected. When there isn't an exact match VLOOKUP always selects the next lowest value, I want it to select the next largest. This is the formula I'm using right now. =VLOOKUP(B18,sixty,2) With B18 being the formula result, and sixty the data range on the next worksheet. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get VLOOKUP to look for the next greater value
kingcole wrote:
I have a worksheet with a bunch of pull down menus where data can be selected and then inputed into a formula. I then want to take the formula and automatically have a part# selected. When there isn't an exact match VLOOKUP always selects the next lowest value, I want it to select the next largest. This is the formula I'm using right now. =VLOOKUP(B18,sixty,2) With B18 being the formula result, and sixty the data range on the next worksheet. Use match+index. Something like: =INDEX(sixty,match(b18,sixty,-1)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get VLOOKUP to look for the next greater value
Thanks for the post but that doesn't seem to work because the table I have
with the value I want to select is on another worksheet. Any other ideas? "Paul Lautman" wrote: kingcole wrote: I have a worksheet with a bunch of pull down menus where data can be selected and then inputed into a formula. I then want to take the formula and automatically have a part# selected. When there isn't an exact match VLOOKUP always selects the next lowest value, I want it to select the next largest. This is the formula I'm using right now. =VLOOKUP(B18,sixty,2) With B18 being the formula result, and sixty the data range on the next worksheet. Use match+index. Something like: =INDEX(sixty,match(b18,sixty,-1)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I get VLOOKUP to look for the next greater value
kingcole wrote:
Thanks for the post but that doesn't seem to work because the table I have with the value I want to select is on another worksheet. Any other ideas? What???? Neither INDEX nor MATCH require the table to be on the same worksheet. So if it doesn't work it is for some reason other than that "the table I have with the value I want to select is on another worksheet" I just tried it and it works fine. Suppose you post what you are doing and we look at what's wrong with it? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup must return a value that is greater than | Excel Worksheet Functions | |||
Matching Values from an Array | Excel Worksheet Functions | |||
vlookup - finding the next value that is GREATER than the lookup value? | Excel Worksheet Functions | |||
Vlookup but also equal to and greater than? | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |