Lookup less than a number

[Rod]

Hello.

I would like to be able to do the equvivalent of:
look up a number in range MaxTermAvail and if the number is < the left value
then bring back the next col; something like

Vlookup (B2, if B7<the left value(MaxTermAvail), 2, false).

So, if B2=61 then the value returned should be 15, when
MaxTermAvail=
41 35
46 30
61 20
66 15
71 10

Thanks.

 

[T. Valko]

Hmmm...

Question:

What if B2 is <41 or is >=71?

 

[Rod]

The left Col is the clients age. So, if <41 the the returned value should be
35; if >=71 then 0.

 

[T. Valko]

Assume your table is in the range G2:H6.

Try this:

=IF(B2<41,35,IF(B2>=71,0,INDEX(H2:H6,MATCH(B2,G2:G6)+1)))

 

[Teethless mama]

-10000 35
41 35
46 30
61 20
66 15
71 10
10000 0

=INDEX(D17,MATCH(B2,C1:C7)+1)

 

[Rod]

Perfect! Thanks!

Assume your table is in the range G2:H6.

Try this:

=IF(B2<41,35,IF(B2>=71,0,INDEX(H2:H6,MATCH(B2,G2:G6)+1)))

 

[T. Valko]

You're welcome. Thanks for the feedback!