Not sure what function/formula required for retrieving data from multiple list selections

[TrixExcel]

Hi!

I am trying to work out which function/formula I need to extract the correct data in the same worksheet. For example if I have selected "Male" from the first drop down menu "Gender", then"11-18 yrs" from the next drop down menu "Age Range" then a physical activity level of "sedentary" from PAL menu, I need the equation cell to draw on the correct equation for a male, 11-18 yrs who is sedentary. If it would help I can show you my spreadsheet.

Thanks! Hope someone can help!

[kersplash]

If it would help I can show you my spreadsheet.

Yes! It would.

[TrixExcel]

OK I'll try. I've not uploaded a spreadsheet here before.

The idea is I would choose options female or male by actual body weight or AIBW (adjusted ideal body weight), then age range which determines which equation will be used then multiply equation by PAL (physical activity level) eg Male AIBW --> 11-18 yrs --> reference correct cell with equation then multiply by PAL of very sedentary (1.3) = final value (EER).. Hope that makes sense! Easier to understand when you look at the spreadsheet. I have included in my spreadsheet an example as I am having to somewhat manually write out the final EER equation in cell B24.

[davsth]

You need to say what the expect result is. How does PAL relate to the rest of the formula?

[davsth]

=OFFSET(C28,MATCH(A19,$A$30:$A$60,0)+MATCH(B19,B30:B35,0),0)*IF(LEFT(A19,1)="M",INDEX(D30:D37,MATCH(C19,$F$30:$F$37,0)),INDEX(E30:E37,MATCH(C19,$F$30:$F$37,0)))

but it is messy how you have structured your data

[Bosco]

Or............

For "EER", in B24 enter formula :

=OFFSET(C28,MATCH(A19,A30:A60,0)+MATCH(B19,B30:B35,0),0)*INDEX(D30:E37,MATCH(C19,F30:F37,0),(LEFT(A19)="F")+1)*1000

Regards
Bosco

[TrixExcel]

=OFFSET(C28,MATCH(A19,$A$30:$A$60,0)+MATCH(B19,B30:B35,0),0)*IF(LEFT(A19,1)="M",INDEX(D30:D37,MATCH(C19,$F$30:$F$37,0)),INDEX(E30:E37,MATCH(C19,$F$30:$F$37,0)))

but it is messy how you have structured your data

I know, sorry!

[TrixExcel]

Or............

For "EER", in B24 enter formula :

=OFFSET(C28,MATCH(A19,A30:A60,0)+MATCH(B19,B30:B35,0),0)*INDEX(D30:E37,MATCH(C19,F30:F37,0),(LEFT(A19)="F")+1)*1000

Regards
Bosco

Thanks Bosco!!! It works fantastic, will make my work day much more efficient!