Looking up values on seperate worksheet to insert in formula

I just got help calculating an age to the a .5 month, that will be used to look up 3 values for that given age in months and gender (1 for Male, 2 for females)on a seperate worksheet.

I must be able to look up the appropriate L (Box Cox) M (median) and S (standard deviation) from a spreadsheet in the same workbook named, LMS for BMI, for a given gender and AGE Months. I know this has to be linked somehow. This spreadheet has like 400 rows, so it has to go done and find the correct Agemos for a given gender, to pull out the L,M, and S Values to use in calculation of Z. If I could get the first line right, I can just drag it down the 600 kids in my spreadsheet.

the LMS worksheet would look like this:
A B C D E F G
Gender AgeMons BMI L M S Zscore
1 147.5 22.18 -2.41592 17.97544 0.13224 1.246242978

I know it probably needs some if/ then coding, but this too is way above my head. Like if Gender =1 and AgeMons = 147.5, then use this L,M,and S in the formula for Z below.

These values are used for computing a Z-score for a given gender (male) at 147.5 months. I guess all this code has to go into the first cell under the Z-Score, or maybe it goes somewhere else, I don't really have a clue.

The formula to compute z is and I apologize if I don't put the correct tags around the code, because I don't know what a tag is.

=((C4/E4)^D4-1)/(D4*F4)

I know this compute z correctly because I have checked it at the Baylor Childrens Hospital Z BMI Calculator.

If anyone could help me figure out how to do this, you would be helping me fight the epidemic of childhood obesity with a better than 66% success rate.

If this is too much to ask for on a forum I understand.

Hi Tim,

Perhaps try this array formula to get the "L":

=INDEX('LMS for BMI'!C:C,MATCH($I2&$H2,'LMS for BMI'!$A:$A&'LMS for BMI'!$B:$B,0))

Since this is an array formula, after typing or editing it you must press CTRL+SHIFT+ENTER, not just ENTER. To get the M and S, just change the red C:C to D:D and E:E, respectively.

Hope that helps!