Need a formula to pull data from another sheets chart based off age and gender

[tbingham]

I have a PT score sheet where i need the Percentage of each PT assessment to pull from the score chart laid out on other sheets by gender and columned by age. Can someone help! I have attached my worksheet.

[oeldere]

you did not add an example so maybe like this?

[tbingham]

I'm sorry i guess the example would be on sheet one says applicants where we put in their age and gender and show thier scores. So for column E i need this special formula to look at thier age and gender then find the value of say column D in the appropriate gender and age group sheet and put the percentage. So example the first guy is 25 year old male lets say he does 30 pushups... i would go to sheet 3 and look for his age range column, how many he did (30) which ends up right between 40-45% then that 45% would go back to his box under Column E on sheet one as his earned PT percentage on Pushups? Hopefully that makes sense

[oeldere]

Change the headers of the table (see the attached file).

E3 =IFERROR(IF(B3="M",INDEX('Male Pushup'!$A$1:$I$22,2,MATCH(Applicants!$C3,'Male Pushup'!$A$1:$I$1,1)),INDEX('Female Pushup'!$A$1:$I$22,2,MATCH(Applicants!$C3,'Female Pushup'!$A$1:$I$1,1))),"")

You can also choose to work with defined names in your formula.

See the attached file.

[tbingham]

So your formula you put in works for the fist guy as 63% but if i enter down and try another test it doenst pull the right percentage. You mention to change the headers of the table what did you mean on that?

[oeldere]

another test it doenst pull the right percentage.

Which test, what do you expect, what result do you get?

[tbingham]

i would expect if i saw a subject is lets say 35 years old and male and he did 42 pushups it would look and see that he is male, 35 then go to the male situp sheet and find his age column find 42 pushups and display that percentage in the Pushup percentage box on sheet 1

[oeldere]

I re arange the data (see the sheet output).

E3 =VLOOKUP(TEXT(Floor(C3,5),"00")&TEXT(D3,"00"),Output!$D$1:$E$170,2,1)

See the green cell in the attached file.

[oeldere]

Also added the Push up for female (sheet output 2)

B2 =IF($B2="","",VLOOKUP(TEXT(FLOOR($C2,5),"00")&TEXT($D2,"00"),IF($B2="M",MALE,IF($B2="F",Female,"")),2,1))

Male is a defined name => =Output!$D$1:$E$191

Female is a defined name => =Output!$F$1:$G$191

See the attached file.

[tbingham]

Thank you for the help ill try this!

[oeldere]

If you like the idea, this file also has the other data in the re-aranged format.

[bebo021999]

Assum all lookup tables are in same construction, from A1:I22
I found out the logic that for each table range of score in Push up and Sit up, for each age range, scores are decreasing (Except Run, increasing)
If that all true:
I use name range "rng" to get relevant range of scores under M/F and age:
Stay in D3 , Ctrl-F3 to apply name:

Please Login or Register  to view this content.

In D3, normally use: INDEX('Male Pushup'!$A$2:$A$22,MATCH(D3,Rng,0)) to get the EXACT score; but with case of: "Male Pushup", score 43, the existing range is 41 & 44, then take MEDIAN of 70 & 75 percent, which rounds to 73

The finally formula in D3:

Please Login or Register  to view this content.

Copy to whole column and accross to column G

In I3:

Please Login or Register  to view this content.

Copy to whole column
For the last person "Sean", I don't know how to do with "27:00", I temporily leave it blank.