I'm currently using the LOOKUP function to return a corresponding value,
however if the number being looked for falls between 2 numbers in the list,
the lower of the two numbers is returned.

Is it possible for the higher of the two to be returned?

Hi,
There isn't a way to do it with VLOOKUP.
VLOOKUP will return the closest match - not the lowest.
--
Ken Hudson


"jonrtait" wrote:

I'm currently using the LOOKUP function to return a corresponding value,
however if the number being looked for falls between 2 numbers in the list,
the lower of the two numbers is returned.

Is it possible for the higher of the two to be returned?

One way is to look (maybe twice):

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) ,1+MATCH(C1,A1:A10,1)))

(all one cell)

I put my table in A1:B10, the value to lookup in C1.

jonrtait wrote:

I'm currently using the LOOKUP function to return a corresponding value,
however if the number being looked for falls between 2 numbers in the list,
the lower of the two numbers is returned.

Is it possible for the higher of the two to be returned?

--

Dave Peterson

Dave - perfect.

It's been bugging me all day and you've cracked it for me.

Thanks very much!

"Dave Peterson" wrote:

One way is to look (maybe twice):

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) ,1+MATCH(C1,A1:A10,1)))

(all one cell)

I put my table in A1:B10, the value to lookup in C1.

jonrtait wrote:

I'm currently using the LOOKUP function to return a corresponding value,
however if the number being looked for falls between 2 numbers in the list,
the lower of the two numbers is returned.

Is it possible for the higher of the two to be returned?

--

Dave Peterson

Dave,

Thanks




"Dave Peterson" wrote:

One way is to look (maybe twice):

=INDEX(B1:B10,
IF(ISNUMBER(MATCH(C1,A1:A10,0)),MATCH(C1,A1:A10,0) ,1+MATCH(C1,A1:A10,1)))

(all one cell)

I put my table in A1:B10, the value to lookup in C1.

jonrtait wrote:

I'm currently using the LOOKUP function to return a corresponding value,
however if the number being looked for falls between 2 numbers in the list,
the lower of the two numbers is returned.

Is it possible for the higher of the two to be returned?

--

Dave Peterson