Can't lookup scores-file attached- (Marines weren't built to create Excel spreadsheets)

[SemperFiDevilDog]

I'm new to excel, and I'm afraid I've bitten off more than I can chew. Excel classes weren't given on Paris Island!

I'm trying to create a program to score a test that I use. The test consists of ten subtests. When a person takes the test, he gets a raw score for each test, so he has ten scores.

To score the test, I take each raw score and convert it to a scaled score, which is based on the person's age.

I have a series of tables, one per sheet, that represent the different age groups (e.g., 16-17, 18-19, 20-24, etc.). All the tables are the same, the first column is raw scores, 0-100. Each row is a different subtest, and there are ten subtests. The only difference in the tables is that the scaled scores are different for each age group. None of the age groups overlap. To calculate the score, you find the person's age, and reference the appropriate table to convert the raw score to a scaled score. It sounds so easy as I type it, but it's kicking my a**!

I have created a formula that will work for the first table (Ages 16-17):
=IF(AND(B3>=16,B3<=17),LOOKUP(B9,Ages1617[RawScore],'Ages 16-17'!B2:B68))

EDIT: This formula is located in cell C9 on the Summary Sheet.

However, I have been unable to add more if statements to this, and so if you're not 16 or 17, I can't score the test. All attempts are met with errors.

I've read about other commands, such as MATCH, INDEX, INDIRECT, etc., but I'm having trouble applying them to my situation.

What I would like to do is have it where I can enter the person's age on the Summary sheet I've created. Underneath, I'd like to enter the ten raw scores in one column, and have Excel look up the appropriate scaled score in the other column.

Thanks in advance for any help you can provide. I'm quite frustrated now, as I've been working on this for many hours. I apologize in advance if this doesn't make sense. Take care, and thanks for your help!