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.

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))

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))
>
>
>

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))
>
>
>

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))
>
>
>

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?