Thread: Lookup "greater than or equal to" in lookup array

This is my first post to the discussion group, so hi all.

I'd like to be able to use a vlookup to return a value where the first
column of the lookup array is "greater than or equal" to the value of the
lookup cell. Using TRUE, of course, returns a "less than or equal to" value.
Does anyone know if there is something similar for "grater than"?

Thanks

Phil

index and match..
--
paul
remove nospam for email addy!



"icemouse" wrote:

> This is my first post to the discussion group, so hi all.
>
> I'd like to be able to use a vlookup to return a value where the first
> column of the lookup array is "greater than or equal" to the value of the
> lookup cell. Using TRUE, of course, returns a "less than or equal to" value.
> Does anyone know if there is something similar for "grater than"?
>
> Thanks
>
> Phil

Sort the table descending and use

=MATCH((LookupValue,LookupArray,-1)

This gives you the relative position in the table; use INDEX() to retrieve
the item you need

--
Kind regards,

Niek Otten

"icemouse" <icemouse@discussions.microsoft.com> wrote in message
news:4788BC5F-88AF-41C7-917A-727BF9851193@microsoft.com...
> This is my first post to the discussion group, so hi all.
>
> I'd like to be able to use a vlookup to return a value where the first
> column of the lookup array is "greater than or equal" to the value of the
> lookup cell. Using TRUE, of course, returns a "less than or equal to"
> value.
> Does anyone know if there is something similar for "grater than"?
>
> Thanks
>
> Phil

Thanks, it works a treat!!


"Niek Otten" wrote:

> Sort the table descending and use
>
> =MATCH((LookupValue,LookupArray,-1)
>
> This gives you the relative position in the table; use INDEX() to retrieve
> the item you need
>
> --
> Kind regards,
>
> Niek Otten
>
> "icemouse" <icemouse@discussions.microsoft.com> wrote in message
> news:4788BC5F-88AF-41C7-917A-727BF9851193@microsoft.com...
> > This is my first post to the discussion group, so hi all.
> >
> > I'd like to be able to use a vlookup to return a value where the first
> > column of the lookup array is "greater than or equal" to the value of the
> > lookup cell. Using TRUE, of course, returns a "less than or equal to"
> > value.
> > Does anyone know if there is something similar for "grater than"?
> >
> > Thanks
> >
> > Phil
>
>
>