Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - Lookup required - not sure!
Hiya,
I didn't get a response earlier so I'm hoping if I post again with a bit more detail, some kindly soul might take pity on my extreme lack of knowledge of Excel functionality and help me out of a situation where I am rapidly losing my hair and my sanity. Ok, here is my "rating matrix". The fees list is contained in a drop down box. User selects one option. The limits of indemnity (loi) (£250k, £500k, 1m and 2m) are check boxes. The user can select one to four loi options. OK, based on the fees selected and the limit of indemnity(s) selected: limits of indemnity Fees £250k £500k £1m £2m 0 to 50000 125 135 150 250 50001 to 100000 165 180 200 300 100001 to 150000 200 225 250 350 150001 to 200000 225 250 275 375 200001 to 250000 250 275 300 400 250001 to 300000 275 300 325 425 300001 to 350000 300 325 350 450 350001 to 400000 325 350 375 475 400001 to 450000 350 375 400 500 450001 to 500000 375 400 425 535 Ok, so what I need from this info is another row/column to populate with the following info:- LOI Premium Result Result Result Result Result Result Result Result For example, fees selected 0 to 50000 all loi options selected LOI Premium 250k 125 500k 135 1m 150 2m 250 I can do one result but all four is beyond my intelligence. Over to you guys. Many thanks in advance and thanks for taking the time to read this incredibly long post. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help please - Lookup required - not sure!
Gillian,
USe a VLOOKUP formula but include all the columns of data, and change the lookup column from 2 to 3 to get the second (135 ) amount, then use 4, then use 5. =VLOOKUP(value,$A$1:$E$11,2) =VLOOKUP(value,$A$1:$E$11,3) =VLOOKUP(value,$A$1:$E$11,4) =VLOOKUP(value,$A$1:$E$11,5) Where Table is this, entered into cells $A$1:$E$11 Value £250k £500k £1m £2m 0 125 135 150 250 50001 165 180 200 300 100001 200 225 250 350 150001 225 250 275 375 200001 250 275 300 400 250001 275 300 325 425 300001 300 325 350 450 350001 325 350 375 475 400001 350 375 400 500 450001 375 400 425 535 You can use a cell reference, say, F2 to hold the value: =VLOOKUP($F$2,$A$1:$E$11,2) etc. HTH, Bernie MS Excel MVP "GillianX" wrote in message ... Hiya, I didn't get a response earlier so I'm hoping if I post again with a bit more detail, some kindly soul might take pity on my extreme lack of knowledge of Excel functionality and help me out of a situation where I am rapidly losing my hair and my sanity. Ok, here is my "rating matrix". The fees list is contained in a drop down box. User selects one option. The limits of indemnity (loi) (£250k, £500k, 1m and 2m) are check boxes. The user can select one to four loi options. OK, based on the fees selected and the limit of indemnity(s) selected: limits of indemnity Fees £250k £500k £1m £2m 0 to 50000 125 135 150 250 50001 to 100000 165 180 200 300 100001 to 150000 200 225 250 350 150001 to 200000 225 250 275 375 200001 to 250000 250 275 300 400 250001 to 300000 275 300 325 425 300001 to 350000 300 325 350 450 350001 to 400000 325 350 375 475 400001 to 450000 350 375 400 500 450001 to 500000 375 400 425 535 Ok, so what I need from this info is another row/column to populate with the following info:- LOI Premium Result Result Result Result Result Result Result Result For example, fees selected 0 to 50000 all loi options selected LOI Premium 250k 125 500k 135 1m 150 2m 250 I can do one result but all four is beyond my intelligence. Over to you guys. Many thanks in advance and thanks for taking the time to read this incredibly long post. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP REQUIRED | Excel Discussion (Misc queries) | |||
Help required | Excel Worksheet Functions | |||
If & Lookup & match Formula Required! | Excel Discussion (Misc queries) | |||
Some sort of lookup formula required | Excel Worksheet Functions | |||
Help Required.... | Excel Discussion (Misc queries) |