Help with lookups

[Ocleron]

Hello, I'm trying to make a spreadsheet that checks age and sex and then use the appropriate score sheet depending on what the age and sex are. If you look on sheet 1 in the workbook in cell E3 or C3 you can see where I've started to next my if statements to do the checks. I was wondering if there is an easier way to accomplish these checks and then use the appropriate sheet. I haven't fill out all the score cards yet, but the sheets have been created. Any help is greatly appreciated thank you!

https://drive.google.com/file/d/1cWOf_tuBonF5MW0BySQpHX1fRIGhZcLo/view?usp=sharing

 

[NiMip]

Not sure of your intent to introduce dates or anything, but this is a bit of a workaround for what I think you're after. I've created a new sheet called "Lookup Sheet", and on that sheet is the following:

[TABLE="class: grid, width: 531"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Sex[/TD]
[TD]Lower Age[/TD]
[TD]Sheet Name[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]M[/TD]
[TD="align: right"]1[/TD]
[TD]Male <30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]M[/TD]
[TD="align: right"]30[/TD]
[TD]Male 30-39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]M[/TD]
[TD="align: right"]40[/TD]
[TD]Male 40-49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]M[/TD]
[TD="align: right"]50[/TD]
[TD]Male 50-59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]M[/TD]
[TD="align: right"]60[/TD]
[TD]Male >60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]1[/TD]
[TD]Female <30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]30[/TD]
[TD]Female 30-39[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]40[/TD]
[TD]Female 40-49[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]50[/TD]
[TD]Female 50-59[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]F[/TD]
[TD="align: right"]60[/TD]
[TD]Female >60[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Back on sheet "1", I've done some rearranging and added some stuff:

[TABLE="class: grid, width: 741"]
<colgroup><col><col><col><col span="4"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD="align: right"]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Age[/TD]
[TD="align: right"]30[/TD]
[TD][/TD]
[TD]Sex[/TD]
[TD]M[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Column:[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Date[/TD]
[TD]Body Comp[/TD]
[TD]Score[/TD]
[TD]Push-ups[/TD]
[TD]Score[/TD]
[TD]Situps[/TD]
[TD]Score[/TD]
[TD]1.5 Mile Run[/TD]
[TD]Score[/TD]
[TD]Total Score[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]16-Nov-17[/TD]
[TD]31.5[/TD]
[TD]20[/TD]
[TD]55[/TD]
[TD]9.5[/TD]
[TD]55[/TD]
[TD]10[/TD]
[TD]12:05[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The formula for the score in C5 is: =INDEX(INDIRECT("'"&INDEX('Lookup Sheet'!$E$4:$E$13,MATCH('1'!$E$1,'Lookup Sheet'!$C$4:$C$13,0)+MATCH('1'!$B$1,'Lookup Sheet'!$D$4:$D$8,1)-1)&"'!"&C3&":"&C3),MATCH(B5,INDIRECT("'"&INDEX('Lookup Sheet'!$E$4:$E$13,MATCH('1'!$E$1,'Lookup Sheet'!$C$4:$C$13,0)+MATCH('1'!$B$1,'Lookup Sheet'!$D$4:$D$8,1)-1)&"'!"&B3&":"&B3),0))

This can be copy and pasted into E5 and G5. I've added the Column values in row 3 based on where you've positioned your data on each of the tabs. This will need to be manually changed if you rearrange those other sheets (I could automate it, but your column headings don't match)