I'm creating a spreadsheet for a client and I'm not sure what
formula/function I need to do this:

I created a table with over 500 available values. I then created (on a
separate sheet) a form to input the customers information. 4 pieces of the
customer's information need to be used to figure out the correct price - Age,
Tobacco or No, Male or Female, and amount of coverage.

I need to be able to reference the right cell in my table according to how
the 4 above criteria are met, and plug that value back on to my customer
information page to show the customer their cost for choosing that amount of
coverage.

Hope that makes sense and any help will be appreciated :-)
--
Thanks,
mekus31

For multi-criteria retrievals, you could use an array-entered* INDEX/MATCH,
something like this:

=INDEX(UnitCost,MATCH(1,(Age=G2)*(Smoker=H2)*(Sex= I2)*(CoverAmt=J2),0))

where UnitCost, Age, Smoker, Sex, CoverAmt are all identically sized defined
col ranges in your reference table, and G2:J2 contains the respective 4
variable inputs. UnitCost is what you want retrieved (the INDEX(UnitCost, ..
part) given the inputs for the rest of the 4 variables.

*Array-enter to press CTRL+SHIFT+ENTER to confirm the formula, instead of
just pressing ENTER. Correctly done, Excel will wrap curly braces { } around
the formula -- see this happening (within the formula bar) as a visual check
that it's correctly array-entered.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mekus31" wrote:
I'm creating a spreadsheet for a client and I'm not sure what
formula/function I need to do this:

I created a table with over 500 available values. I then created (on a
separate sheet) a form to input the customers information. 4 pieces of the
customer's information need to be used to figure out the correct price - Age,
Tobacco or No, Male or Female, and amount of coverage.

I need to be able to reference the right cell in my table according to how
the 4 above criteria are met, and plug that value back on to my customer
information page to show the customer their cost for choosing that amount of
coverage.

Hope that makes sense and any help will be appreciated :-)
--
Thanks,
mekus31

I appreciate the response Max.
I tried it but unfortunately I think that stuff is way over my head. I've
figured out a different way to do it (all 1680 possibilities, ugh) by using
the IF command but now I'm having a problem with the value showing false when
I take out the coverage amount (in case they don't want to buy that
endorsement). Is there a way to tell it not to show "False," only a value if
criteria is met?

BTW where do you learn all the advanced stuff? I have all the usual Step by
Step and Excel for Dummies books, but they're pretty much beginners books.
Thanks again
--
Thanks,
mekus31


"Max" wrote:

For multi-criteria retrievals, you could use an array-entered* INDEX/MATCH,
something like this:

=INDEX(UnitCost,MATCH(1,(Age=G2)*(Smoker=H2)*(Sex= I2)*(CoverAmt=J2),0))

where UnitCost, Age, Smoker, Sex, CoverAmt are all identically sized defined
col ranges in your reference table, and G2:J2 contains the respective 4
variable inputs. UnitCost is what you want retrieved (the INDEX(UnitCost, ..
part) given the inputs for the rest of the 4 variables.

*Array-enter to press CTRL+SHIFT+ENTER to confirm the formula, instead of
just pressing ENTER. Correctly done, Excel will wrap curly braces { } around
the formula -- see this happening (within the formula bar) as a visual check
that it's correctly array-entered.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mekus31" wrote:
I'm creating a spreadsheet for a client and I'm not sure what
formula/function I need to do this:

I created a table with over 500 available values. I then created (on a
separate sheet) a form to input the customers information. 4 pieces of the
customer's information need to be used to figure out the correct price - Age,
Tobacco or No, Male or Female, and amount of coverage.

I need to be able to reference the right cell in my table according to how
the 4 above criteria are met, and plug that value back on to my customer
information page to show the customer their cost for choosing that amount of
coverage.

Hope that makes sense and any help will be appreciated :-)
--
Thanks,
mekus31

.. Is there a way to tell it not to show "False," only a value if
criteria is met?

Perhaps you could post your current IF formula ..
Being specific would certainly help attract responses

BTW where do you learn all the advanced stuff?

well, you could start learning right-here in these excel newsgroups by
reading & trying out the responses to the many posts. takes effort, but
there's great variety around both in queries asked and responses given.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mekus31" wrote:
I appreciate the response Max.
I tried it but unfortunately I think that stuff is way over my head. I've
figured out a different way to do it (all 1680 possibilities, ugh) by using
the IF command but now I'm having a problem with the value showing false when
I take out the coverage amount (in case they don't want to buy that
endorsement). Is there a way to tell it not to show "False," only a value if
criteria is met?

BTW where do you learn all the advanced stuff? I have all the usual Step by
Step and Excel for Dummies books, but they're pretty much beginners books.
Thanks again
--
Thanks,
mekus31

Ok Max, what I thought would work isn't working anyway. This is what I'm
trying to do:

Sheet 1 questions:
Gender: M or F
Tobacco: Y or N
Age: (options are) 18-59
Coverage desired: (options are) 5,000 - 50,000 increments of 5

Sheet 2:
A table with 1680 possible combinations depending on how they answer gender,
tobacco, age, & coverage amt

What I'm trying to do, for example, if the above answers are Male(M), No
Tobacco(N), age 25(25), and $5,000 (coverage amt) I want the cell with the
price to answer $6.80 which will be the monthly charge they have to pay.

You see, what I'm trying to get it to do is look at the values in 4 cells,
to determine what the value should be for the final cell(using my table on
the second sheet). I wish I could input my spreadsheet here, it would be way
easier to explain.

Hopefully I explained it ok. I downloaded the example you referred me to and
am trying to figure it out. If only my client would use Access, this would be
much easier.


--
Thanks,
mekus31


"Max" wrote:

.. Is there a way to tell it not to show "False," only a value if
criteria is met?

Perhaps you could post your current IF formula ..
Being specific would certainly help attract responses

BTW where do you learn all the advanced stuff?

well, you could start learning right-here in these excel newsgroups by
reading & trying out the responses to the many posts. takes effort, but
there's great variety around both in queries asked and responses given.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mekus31" wrote:
I appreciate the response Max.
I tried it but unfortunately I think that stuff is way over my head. I've
figured out a different way to do it (all 1680 possibilities, ugh) by using
the IF command but now I'm having a problem with the value showing false when
I take out the coverage amount (in case they don't want to buy that
endorsement). Is there a way to tell it not to show "False," only a value if
criteria is met?

BTW where do you learn all the advanced stuff? I have all the usual Step by
Step and Excel for Dummies books, but they're pretty much beginners books.
Thanks again
--
Thanks,
mekus31

Perhaps you could use either of the 2 free filehosts listed below to upload
your sample file and then post the link to it in response here (the link is
generated when you upload, just copy and paste it here):

http://www.flypicture.com/
http://cjoint.com/index.php

For cjoint.com (it's in French), just click the "Browse" button, navigate to
folder select the file Open, then click the button centred in the page
below labelled "Creer le lien Cjoint") and it'll generate the link. Then
copy & paste the generated link as part and parcel of your response here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"mekus31" wrote:
Ok Max, what I thought would work isn't working anyway. This is what I'm
trying to do:

Sheet 1 questions:
Gender: M or F
Tobacco: Y or N
Age: (options are) 18-59
Coverage desired: (options are) 5,000 - 50,000 increments of 5

Sheet 2:
A table with 1680 possible combinations depending on how they answer gender,
tobacco, age, & coverage amt

What I'm trying to do, for example, if the above answers are Male(M), No
Tobacco(N), age 25(25), and $5,000 (coverage amt) I want the cell with the
price to answer $6.80 which will be the monthly charge they have to pay.

You see, what I'm trying to get it to do is look at the values in 4 cells,
to determine what the value should be for the final cell(using my table on
the second sheet). I wish I could input my spreadsheet here, it would be way
easier to explain.

Hopefully I explained it ok. I downloaded the example you referred me to and
am trying to figure it out. If only my client would use Access, this would be
much easier.

I tried it but unfortunately I think that stuff is way over my head ..

Perhaps try this sample from my archives*
which illustrates a multi-criteria INDEX/MATCH in action:
http://savefile.com/files/555218
Matching multiple criteria.xls

*Link is in my sign-off. 100's of samples available for easy reference.
All FOC, all are welcome.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---