Formula from DropDown Lists?

**ashley.butler** · 03-04-2014, 01:28 PM

I am trying to come up with a formula that will take the values chosen on 2 drop down list to populate a chosen amount on another cell.
Example. Cell C4 you choose whether you are Male or Female. Cell C5, you choose whether you are 19-30, 31-50, or 51+.

Servings Recommended: From there I want cell C12 to fill in As 2 Cups if they choose Female and 19-30
1.5 Cups if they choose Female and 31-50 or 51+
and 2 Cups if they choose Male (no matter the age)

Can someone help me out please??!

Thanks

**AliGW** · 03-04-2014, 01:35 PM

In C12:

=IF(AND(C4="Female",C5="19-30"),"1.5.Cups","2 Cups")

**ashley.butler** · 03-04-2014, 01:41 PM

That is amazing! Thank you. So, for one a bit more complicated would it work the same way or would I need another option.

Using the same factors (Male/Female) and Age.

Put I need servings to be different Female 19-30 and Female 31-50=2.5 Cups
Female 51+=2 Cups
Male 19-30 and 31-50=3 Cups
Male 51+= 2.5 Cups

Thank in advance!

**AliGW** · 03-04-2014, 01:52 PM

Can be done with an IF formula, but I'd create a little table and use VLOOKUP.

However:

=IF(AND(C4="Male",OR(C5="19-30",C5="31-50")),"3 Cups",IF(AND(C4="Female",C5="51+"),"2 Cups","2.5 Cups"))

**ashley.butler** · 03-04-2014, 01:55 PM

Thank you again! I will also look into VLOOKUP. Not sure how that works either so I may be back asking more questions!

**AliGW** · 03-04-2014, 01:57 PM

Make sure you found the amended slimline formula in my last post!

**ashley.butler** · 03-04-2014, 03:16 PM

Me again!
When you give me a formula, I get it! But then when I try to edit it for me next Line, I keep messing it up because there are small changes! So, I am going to go ahead and throw out there the next 3 I need in hopes you are still able to help me because I am so frustrated with myself not being able to figure this out!

A)
Female Ages 19-30 and 31-51=6 Ounces
Female Ages 51+= 5 Ounces
Male Ages 19-30=8 Ounces
Male Ages 31-50=7 Ounces
Male Ages 51+=6 Ounces

B)
Female ages 19-30=5.5 Ounces
Female ages 31-50 and 51+=5 Ounces
Male ages 19-30=6.5 Ounces
Male Ages 31-50=6 Ounces
Male Ages 51+=5.5 Ounces

Last one is a change and may need a different approach?
cell C6 is activity level (Either Moderately Active or Sedentary)
So I need these to be based on C4, C5, and C6
Female 19-30 Mod. Active=2,000-2,200 Calories
Female 31-50 Mod. Active=2,000 Calories
Female 51+ Mod. Active=1,8000 Calories
Female 19-30 Sedentary=1,800-2,000 Calories
Female 31-50 Sedentary=1,800 Calories
Female 51+ Sedentary=1,600 Calories
Male 19-30 Mod. Active=2,600-2,800 Calories
Male 31-50 Mod. Active=2,400-2,600 Calories
Male 51+ Mod. Active=2,200-2,400 Calories
Male 19-30 Sedentary=2,400-2,600 Calories
Male 31-50 Sedentary=2,200-2,400 Calories
Male 51+ Sedentary=2,000-2,2200 Calories

Any additional help you can give me with this 3 situations would be great! I am trying to make an easy to use sheet for my office workers to help them track intake and hopefully make healthier choices!

Thanks in advance again

**AliGW** · 03-04-2014, 03:56 PM

I'm on the iPAD now, having just scoffed pancakes with lemon juice and sugar with my daughter, it being Shrove Tuesday, so no more Excel for me tonight! I really do think, though, that the added complexity here would warrant a VLOOKUP table, and that's how I'd go about it. If nobody else jumps in to help, I'll offer a suggestion tomorrow.