Problem with the lookup.

[AFB]

Hi,

My problem is that I have a column

A B
1 12 0,10
2 24 0,20
3 36 0,40
4 48 0,70
5 60 0,89

with years(in months). I wish with a lookup value from
column A to return a value from columnn B. This is quite
simple with the lookup function, but the problem is that
having, for rxample in cell C20 the lookup value 36 it
would return 0,40, but having a lookup value of 37 to
return 0,70, and it still returns me the 0,40. Is there
another function that returns me the exact value (in this
case 36 is an exact value), but if the value is higher
than 36 (until 48) would return me the next interval and
so on.

Thank You for any help you could provide me.

AFB

(Lisbon - Portugal)

 

[Don Guillett]

Look in HELP index for vlookup and you will see. Might be easier for you to
re-do your table.

 

[Frank Kabel]

Hi
one way:
- resort your table: column A should be descending
- use the formula
=INDEX(B1:B10,MATCH(C20,A1:A10,-1))

 

[AFB]

..

 

[hj]

Hi AFB,
Not to intrude on Frank Kabel's reply, but I would like
to know more about the set-up.

The way the VLOOKUP function works is to default to a
lower value than the lookup value if there is no match.
Thus, in your example, an input of 37 will cause Access to
use 36; I don't think there's a switch to make it default
to a higher value.
If you can allow inputs ranging from 0 up, you might
try setting the Acol values to the bottom of the lookup
range rather than the top: 0,13,25,37,49 instead of
12,24,etc. An input of 36 would lookup the Bcol value in
the 25 row, etc.

I guess what I'm trying to say in feneral is, your
lookup table should be structured to find values from the
minimum acceptable match on up rather than the maximum
acceptable match on down.

I'd be interested in your reply. Thanks. HJ

 

[Aladin Akyurek]

=INDEX($B$1:$B$5,MATCH(C20,$A$1:$A$5,1)+(C20>LOOKUP(C20,$A$1:$A$5)))