From: Anthony on
Hi,

I'm trying to create a Life Insurance Calculator within Excel. I've already
done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able to
reference back to them given the option. The four options available are:
1. Male Non-Smoker
2. Male Smoker
3. Female Non-Smoker
4. Female Smoker

For example:
1. If Male AND a Non-Smoker, I want to refer to Cell A1
2. If Male AND a Smoker, I want to refer to Cell A2
3. If Female AND a Non-Smoker, I want to refer to Cell A3
4. If Female AND a Smoker, I want to refer to Cell A4

Any help is much appreciated.

Thanks

From: Don Guillett on
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Anthony" <Anthony(a)discussions.microsoft.com> wrote in message
news:B6C75434-45F7-4C5F-9430-6155E43A0AEA(a)microsoft.com...
> Hi,
>
> I'm trying to create a Life Insurance Calculator within Excel. I've
> already
> done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able
> to
> reference back to them given the option. The four options available are:
> 1. Male Non-Smoker
> 2. Male Smoker
> 3. Female Non-Smoker
> 4. Female Smoker
>
> For example:
> 1. If Male AND a Non-Smoker, I want to refer to Cell A1
> 2. If Male AND a Smoker, I want to refer to Cell A2
> 3. If Female AND a Non-Smoker, I want to refer to Cell A3
> 4. If Female AND a Smoker, I want to refer to Cell A4
>
> Any help is much appreciated.
>
> Thanks
>

From: Bob Phillips on
How about

=INDEX(A1:A4,MATCH(selected_option,option_cells,0))

--

HTH

Bob

"Anthony" <Anthony(a)discussions.microsoft.com> wrote in message
news:B6C75434-45F7-4C5F-9430-6155E43A0AEA(a)microsoft.com...
> Hi,
>
> I'm trying to create a Life Insurance Calculator within Excel. I've
> already
> done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able
> to
> reference back to them given the option. The four options available are:
> 1. Male Non-Smoker
> 2. Male Smoker
> 3. Female Non-Smoker
> 4. Female Smoker
>
> For example:
> 1. If Male AND a Non-Smoker, I want to refer to Cell A1
> 2. If Male AND a Smoker, I want to refer to Cell A2
> 3. If Female AND a Non-Smoker, I want to refer to Cell A3
> 4. If Female AND a Smoker, I want to refer to Cell A4
>
> Any help is much appreciated.
>
> Thanks
>


From: Don Guillett on
One way using a data validation restricted cell (g14) with msn,ms,fns,fs
=INDIRECT("a"&LOOKUP(G14,{"fns","fs","mns","ms";3,4,1,2}))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"Anthony" <Anthony(a)discussions.microsoft.com> wrote in message
news:B6C75434-45F7-4C5F-9430-6155E43A0AEA(a)microsoft.com...
> Hi,
>
> I'm trying to create a Life Insurance Calculator within Excel. I've
> already
> done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able
> to
> reference back to them given the option. The four options available are:
> 1. Male Non-Smoker
> 2. Male Smoker
> 3. Female Non-Smoker
> 4. Female Smoker
>
> For example:
> 1. If Male AND a Non-Smoker, I want to refer to Cell A1
> 2. If Male AND a Smoker, I want to refer to Cell A2
> 3. If Female AND a Non-Smoker, I want to refer to Cell A3
> 4. If Female AND a Smoker, I want to refer to Cell A4
>
> Any help is much appreciated.
>
> Thanks
>

From: Anthony on
Thanks guys,

I've spent the last few hours working on this and I figured it out! My
formula as follows:

=IF($B$3="Male", IF($B$4="Non-Smoker", 'Premium Rate Table'!L4, 'Premium
Rate Table'!L5), IF($B$3="Female", IF($B$4="Non-Smoker",'Premium Rate
Table'!L6,'Premium Rate Table'!L7)))

B3 = Drop down list where you can choose "Male" or "Female"
B4 = Drop down lise where you can choose "Non-Smoker" or "Smoker"
'Premium Rate Table' = Separate Worksheet with the Premium Rate Tables.
L4 = Male Non-Smoker Premium Rate
L5 = Male Smoker Premium Rate
L6 = Female Non-Smoker Premium Rate
L7 = Female Smoker Premium Rate
The L# fields already have a VLOOKUP formula to obtain the correct Premium
Rate when the relevant Age is entered.

Thanks for your responses anyway.

Anthony
 |  Next  |  Last
Pages: 1 2
Prev: On Time Calculation
Next: Highlight cells