| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| number lookup | Excel Worksheet Functions | |||
| lookup returns row number-why? | Excel Worksheet Functions | |||
| lookup based on a row number | Excel Worksheet Functions | |||
| Part Number Lookup | Excel Worksheet Functions | |||
| Number Lookup in Matrix | Excel Worksheet Functions | |||
Linear Mode
