LOOKUP

[Andrew]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

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.

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Andrew]

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

 

[T. Valko]

You're welcome!