INDEX, VLOOKUP? What's best for this situation?

[charliedog]

Ok, here’s a simplified version of what I'm trying to do... At the top of the
worksheet the user enters the name of the subject in C1, selects gender from
a drop-down in C2, then selects age from a drop-down in C3. They then answer
a serious of questions (answers selected from drop-downs) and number values
are assigned and totaled based on those answers (I've got that part). Here's
where I'm stuck: when they get to the question of "Sugar Intake" (A10), they
select from a drop-down in B10 (choices are No, Mild, Moderate, Heavy).

Sheet 1 is as follows:
--------------------------------------------
Name: John Smith
Gender: Male
Age: 7

Academics 3.5-3.9 50

Activity High 15

Sugar Intake Mild 12
-----------------------------------------------------

Once they have selected the level of sugar intake, a number will appear in
C10 based on the formula in C10, which currently looks like this:
=IF(C2="Male",INDEX(Sugar!$B$5$14,MATCH(C3,Sugar!$A$5:$A$14,0),MATCH(B11,Sugar!$B$3:$D$3,0)))

This formula works so far and brings the correct number into C10 based on
the chart on the "Sugar" tab, which has a specific number assigned based on
age, gender, and level of sugar intake.

Sugar (Sheet 2):
--------------------------------------------------
Age/Sex Mild Moderate Heavy
Male
5 10 12 15
6 11 13 16
7 12 14 17
8 13 15 18
9 14 16 19
10 15 17 20
11 16 18 21
12 17 19 22
13 18 20 23
14 19 21 24

Female
5 9 10 14
6 10 11 15
7 11 12 16
8 12 13 17
9 13 14 18
10 14 15 19
11 15 16 20
12 16 17 21
13 17 18 22
14 18 19 23
--------------------------------------------
I now need to fix the formula so that if "Female" is selected, it will pull
in the correct figure from the Sugar tab based on the female data (B1726).
Also, if "no" is selected on sheet1, B10, or if the age selected is out of
the age range covered by the Sugar table, I need "0" to display in C10
instead of "#N/A", which is what appears now.

Should the formula use VLOOKUP instead of INDEX? Any advice on the best way
to handle this would be greatly appreciated. I am a novice, learning on the
fly, and don't understand the syntax of these formulas very well. Thanks in
advance for any and all assistance.

 

[John C]

Once solution, would be to set up a little differently. The only thing you
need to do on the Sugar tab is name the 2 tables. Highlight the selected
region for each. Assuming your male data starts with age 5 in A2, and the 14
in D11 (for age 14 heavy intake). So highlight from A211 and name it
SugarM. Do the same for the Female table and call it SugarF.
Then, your formula would be:
=IF(OR(C2="",B10=""),"",IF(OR(C3<5,C3>14,B10="No"),0,VLOOKUP(C3,INDIRECT("Sugar"&LEFT(C2,1)),INDEX(MATCH(B10,Sugar!$A$1:$D$1,0),0),FALSE)))

Now, that being said, I noticed your 2 tables were rather small, and very
linear. You have a age range of 10, and a base line for each age/sugart
intake level. They go up 1 for every year, so, if this is accurate table
(vice a sample table), you could even do away with the table. If that
interests you, let me know, I can work up the formula.

 

[Erik Veldkamp]

You use only one argument in the IF statement, so you can add a second
argument (the "ELSE" argument) "IF(C2="Female", ....")

 

[charliedog]

So close! Using the formula you provided, when I make my selections (e.g.,
"Male" in C2, "10" in C3, and "Moderate" in B10, it shows a 15 in C10, but if
you look at the Sugar table where it's pulling the data, it should be 17. It
seems to be pulling the data from the column to the left, so select "heavy"
and get moderate values, select "moderate" and get mild values, and so on.

Also, to answer your other question, no these are not real values. I decided
to "dumb it down" to simplify. Thanks for the help thus far. Almost there...

 

[charliedog]

Thanks for the suggestion, but I'm not sure what you mean. Is there any way
you could give me the whole formula? I have trouble understanding the syntax
of these formulas and I'm not understanding quite where I would put that
argument, and the precise "punctuation" of it.

 

[John C]

Can you post your formula? Copy it from excel, then paste. For me,
Male:Moderate:10 = 17, not 15.

 

[charliedog]

John, Disregard my previous note, it was my error when I changed the
reference cells. It's working great. You are a GENIUS! Many thanks.

 

[John C]

Genius? Nope. But I appreciate the feedback