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

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!

Ha ha... I like your thread title but there are rules on this forum as well (just like in the Marines ). The title should contain some clue of the problem that you are posting so that Google can find it. That way it can help people to a better life, long in to the future. Put your current one in parenthesis afterwards if possible, I really like it. Also, a posted sample workbook will look much more attractive to problemsolvers.

This formula is to the best of my understanding but it does not fit with your results. Take a look and see if I'm on the right track.
Use the Evaluate Formula button under the Formulas tab to step through the formula.

Glad to hear it works! What I mean by "does not fit with your results" is that there were numbers in the result column from before. I thought maybe those were "manually extracted" numbers to be used as a reference. If not then fine.

Since you did put all your data in Tables I had to make one version using the Table feature as well. Same thing but the formula is a little bit more compact.

Is there a reference? Yes and no. The whole internet and especially this forum . Then there are books about formulas from Walkenbach for instance. The built in help in Excel is handy as well. But no, there is nothing for this particular problem.

Do a lot of Excel excersises and hang around this forum and you will get up to speed. Try each one of the functions I used (INDEX, INDIRECT etc) on their own to get an understanding how they work.
http://www.excelfunctions.net/Excel-...Functions.html

In short here is how it works:
The VLOOKUP is used with the option 1 at the end. This means that it will go through a sorted table until it finds the biggest number that is still smaller than the lookup number. I use that to get the name of the sheet (or table in this version). It's wrapped with an indirect formula which makes Excel look at the result as a name rather than just a textstring. Then the INDEX function which will just give you the result in a table based on "coordinates" (row and column nr). The row number is simply the age offset with a number to fit the table. The column nr I get from ROWS. Note that the first cell reference in ROWS is fixed with $ signs and the second one isn't. That means that it will expand as you copy the formula down, effectively just counting the number of rows the formula have been copied.