Selecting Column with three variablesand lookup

[Jeff]

I am trying to select either of the last two columns dependent on the
selection of the first two columns. The only way i have been able to do
it is by concenating the first two strings and then doing a look up.

Is there a better way to achieve this. Have struggled with Index and
match with no success.

User will select a Region
then Select a Sum Insured
then Select a Cover type ( AD or DE)
And have the Premium returned

Table sample below:


Region SumIns AD DE
Northwest $180,000 $685.46 $891.09
Northwest $185,000 $696.90 $905.98
Northwest $190,000 $708.09 $920.52
Northwest $195,000 $719.02 $934.73
Northwest $200,000 $729.70 $948.61
Northwest Coast $30,000.00 $232.66 $302.46
Northwest Coast $35,000.00 $257.34 $334.54
Northwest Coast $40,000.00 $281.56 $366.02
Northwest Coast $45,000.00 $305.32 $396.91


Any suggestions or hints appreciated.

Jeff

 

[T. Valko]

Assuming you want *exact* Sum Insured amounts. For example: Northwest
$190,000 and not: Northwest $193,250.

See this screencap:

http://img211.imageshack.us/img211/2939/sumproductjl7.jpg

 

[Jeff]

Assuming you want *exact* Sum Insured amounts. For example: Northwest
$190,000 and not: Northwest $193,250.

See this screencap:

http://img211.imageshack.us/img211/2939/sumproductjl7.jpg

Biff,

Appreciate your prompt response , formula is exactly as required.

Out of interest , sum insureds are in $5000 increments , how would you
modify your formula to round the user input amount to the nearest $5000
sum insured.

Regards

Jeff

 

[T. Valko]

Biff,

Appreciate your prompt response , formula is exactly as required.

Out of interest , sum insureds are in $5000 increments , how would you
modify your formula to round the user input amount to the nearest $5000
sum insured.

Regards

Jeff

There are several ways to deal with that. How about making sure they enter
an amount that is a multiple of 5000? You can do this with data validation.

Based on my screencap:

The Sum Insured amount is entered in cell B14
Select cell B14
Goto the menu Data>Validation
Allow: Custom
Formula: =AND(ISNUMBER(B14),B14>0,MOD(B14,5000)=0)
Click the Error Alert tab
You can enter a message like this:

Title: Sum Insured

Error Message: Amount entered must be in multiples of $5000
OK out

Now, if someone (manually**) enters *anything* other than a number that is a
multiple of 5000 this message will appear:

http://img513.imageshack.us/img513/3063/datavalidqg7.jpg

** Data validation can be defeated by copy/paste or drag and drop.