For those who come to this thread in the future. Here is the final result
=INDEX(FILTER(B2_Riboflavin,(Age_Vitamins[Min Range]<=Demographic[Age])*(Age_Vitamins[Max Range]>=Demographic[Age])*(***_Vitamins[***]=IF(Demographic[Pregnant/Lactating?]<>"Neither",Demographic[Pregnant/Lactating?],Demographic[***]))),SEQUENCE(1,3))
The INDEX allows the SEQUENCE function to control how many levels of spill result (in this case 3). The FILTER; filters... Then the array is set: "B2_Riboflavin" (I made each element, vitamin, and trace mineral into it's own table. I also made the age range, and *** ($EX) into it's own table at the beginning of each Nutrition Data sheet chart. Couple more changes: the infant and children ranges were duplicated and added to the beginning of each gender so as to keep things simple.
=INDEX(FILTER(Zinc,(Age_Vitamins[Min Range]<=Demographic[Age])*(Age_Vitamins[Max Range]>=Demographic[Age])*(***_Vitamins[***]=IF(Demographic[Pregnant/Lactating?]<>"Neither",Demographic[Pregnant/Lactating?],Demographic[***])))*IF(Demographic[Vegetarian?]="Vegetarian",1.5,1),SEQUENCE(1,3))
The formula for zinc is slightly different, multiplying the result by 1.5 if the demographic is a vegetarian (because the bio-availability of zinc is lower in plants, than in animals).
Or you can add stuff as is the case with Vitamin C:
=INDEX(FILTER(C_Vitamin,(Age_Vitamins[Min Range]<=Demographic[Age])*(Age_Vitamins[Max Range]>=Demographic[Age])*(***_Vitamins[***]=IF(Demographic[Pregnant/Lactating?]<>"Neither",Demographic[Pregnant/Lactating?],Demographic[***])))+IF(Demographic[Smoker?]="Smoker",35,0),SEQUENCE(1,3))
Because smoking increases oxidative stress and metabolic turnover of vitamin C, the requirement for smokers is increased by 35 mg/day.
Thanks all for your help! SOLVED
Bookmarks