Lookup less than a number
 

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.

Lookup less than a number
 

Hmmm...

Question:

What if B2 is <41 or is =71?

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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.

Lookup less than a number
 

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

"T. Valko" wrote:

Hmmm...

Question:

What if B2 is <41 or is =71?

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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.

Lookup less than a number
 

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:G 6)+1)))

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
The left Col is the clients age. So, if <41 the the returned value should
be
35; if =71 then 0.

"T. Valko" wrote:

Hmmm...

Question:

What if B2 is <41 or is =71?

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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.

Lookup less than a number
 

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

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



"Rod" wrote:

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.

Lookup less than a number
 

Perfect! Thanks!

"T. Valko" wrote:

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:G 6)+1)))

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
The left Col is the clients age. So, if <41 the the returned value should
be
35; if =71 then 0.

"T. Valko" wrote:

Hmmm...

Question:

What if B2 is <41 or is =71?

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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.

Lookup less than a number
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
Perfect! Thanks!

"T. Valko" wrote:

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:G 6)+1)))

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
The left Col is the clients age. So, if <41 the the returned value
should
be
35; if =71 then 0.

"T. Valko" wrote:

Hmmm...

Question:

What if B2 is <41 or is =71?

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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.