LOOKUP
 

Hi,

I have one question concerning a Lookup function. Let suppose that we have
2 columns A and B. This function works fine and returnes a value from
column B which corresponds to a value from column A from the same row when
A value is EQUAL to my input (everything according to description in HELP).
When we have a value which differs from values given in column A (for
instance: in A we have 2, 3, 4, 5, 7, but my input is 4,5) then LOOKUP
returns a value from c. B which corresponds to value 4 from c. A. It rounds
down. I would expect lookup to return a value which corresponds to value 5
from c. A. It means I would like LOOKUP to round up NOT down. Is it
possible in LOOKUP function? Maybe another function should be used by me?

Thanks

Andrew

LOOKUP
 

Lookup will accept ROUNDUP() in its first argument:

=LOOKUP(ROUNDUP(4.5,0),A1:A5,B1:B5)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Andrew" wrote in message
...
Hi,

I have one question concerning a Lookup function. Let suppose that we have
2 columns A and B. This function works fine and returnes a value from
column B which corresponds to a value from column A from the same row when
A value is EQUAL to my input (everything according to description in
HELP).
When we have a value which differs from values given in column A (for
instance: in A we have 2, 3, 4, 5, 7, but my input is 4,5) then LOOKUP
returns a value from c. B which corresponds to value 4 from c. A. It
rounds
down. I would expect lookup to return a value which corresponds to value 5
from c. A. It means I would like LOOKUP to round up NOT down. Is it
possible in LOOKUP function? Maybe another function should be used by me?

Thanks

Andrew

LOOKUP
 

If you want it to "round up" sort your table in descending order.

Instead of this:

2, 3, 4, 5, 7

Do this:

7, 5, 4, 3, 2

And use a formula like this:

=INDEX(C1:C5,MATCH(4.5,A1:A5,-1))

This formula will return the value corresponding to 5. Note that if the
lookup_value is greater than the highest number in the lookup_array you'll
get a #N/A error since there's nothing to "round up" to.


--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
Hi,

I have one question concerning a Lookup function. Let suppose that we have
2 columns A and B. This function works fine and returnes a value from
column B which corresponds to a value from column A from the same row when
A value is EQUAL to my input (everything according to description in
HELP).
When we have a value which differs from values given in column A (for
instance: in A we have 2, 3, 4, 5, 7, but my input is 4,5) then LOOKUP
returns a value from c. B which corresponds to value 4 from c. A. It
rounds
down. I would expect lookup to return a value which corresponds to value 5
from c. A. It means I would like LOOKUP to round up NOT down. Is it
possible in LOOKUP function? Maybe another function should be used by me?

Thanks

Andrew

LOOKUP
 

mmmm....

Until I saw Biff's reply I never noticed that your data was not linear. Go
with Biff.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Lookup will accept ROUNDUP() in its first argument:

=LOOKUP(ROUNDUP(4.5,0),A1:A5,B1:B5)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Andrew" wrote in message
...
Hi,

I have one question concerning a Lookup function. Let suppose that we
have
2 columns A and B. This function works fine and returnes a value from
column B which corresponds to a value from column A from the same row
when
A value is EQUAL to my input (everything according to description in
HELP).
When we have a value which differs from values given in column A (for
instance: in A we have 2, 3, 4, 5, 7, but my input is 4,5) then LOOKUP
returns a value from c. B which corresponds to value 4 from c. A. It
rounds
down. I would expect lookup to return a value which corresponds to value
5
from c. A. It means I would like LOOKUP to round up NOT down. Is it
possible in LOOKUP function? Maybe another function should be used by me?

Thanks

Andrew

LOOKUP
 

Hi Sandy and Biff,

Thanks for your replay. I haven't supposed that lookup would accept
"roundup". Moreover thank you Biff for your idea.

Kind regards

Andrew

LOOKUP
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Andrew" wrote in message
...
Hi Sandy and Biff,

Thanks for your replay. I haven't supposed that lookup would accept
"roundup". Moreover thank you Biff for your idea.

Kind regards

Andrew