LOOKUP and higher value
 

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?

LOOKUP and higher value
 

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?

LOOKUP and higher value
 

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

LOOKUP and higher value
 

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

LOOKUP and higher value
 

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