Help please - Lookup required - not sure!

[GillianX]

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.

 

[Bernie Deitrick]

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