Determining an individual's scaled score from their raw score in a table

[Adamonia]

Hello everyone,

Example file attached. Within the file you will find two lookup tables to the right. The top table has raw scores for boys by age band (i.e., B1 = boy, age band 1). The top table has raw scores for girls by age band (i.e., G1 = girl, age band 1). The scaled scores (SS) for each table are notated in the S column. I would like to auto-calculate the SS for each entry in the E column. Columns A-D provide examples.

Thus, if a boy (coded as 1 in Column A)--use the top table, use the appropriate column based on that boys age band (i.e., 1-12), output the SS based on that individual's raw score. Same thing for girls. Any help that you could provide would be legendary. Big thanks in advance!

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Adamonia]

My apologies, Ali. I edited the thread title--I hope that is better.

[MarvinP]

Hi Adamonia,

This formula looks to do what you want:

Please Login or Register  to view this content.

See it in the attached: Index Match into Offset Range.xlsx

[Adamonia]

Hi Marvin, just outstanding. I am, however, now in a pinch. I have been informed I need to move the reference tables to a different sheet (i.e., Sheet1 and Sheet2). I've updated my attachment. I've tried adapting your formula but am hitting a snag. Don't know if anyone could help, I heavily apologize for wasting your time.

[Eric Barajas]

You can select a the cell (even if in another tab) while inside of the cell/formula you want. or you can add this to the formula manually

Please Login or Register  to view this content.

[Adamonia]

Hi Eric,

I've used the convention to use cells in another sheet/tab (see below; Sheet2!), however, the formula Marvin produced was dependent on the OFFSET function and I'm not sure how to adjust for this when the reference tables have been moved to Sheet2. I've tried a few different adaptations but haven't gotten it to work. In the end, I'm too much an amateur to figure out what MATCH(C2,OFFSET($F$1,0,B2,20),1)) is doing.

=INDEX(Sheet2!$M$1:$M$21,MATCH(C2,OFFSET($F$1,0,B2,20),1))

[Eric Barajas]

Try this

Please Login or Register  to view this content.

[Adamonia]

Massive thanks!! Appreciate it Eric!

[Adamonia]

I've had a new issue pop-up with some different tables. Before, the tables in Sheet2 were both equal in length. Now, one of the tables in Sheet2 has an extra column.

For anyone new to this thread, I'm trying to use the tables on Sheet2 to give the appropriate value on Sheet1 (now the F column).

An example attachment is provided. In the E column, I applied the previously provided formula, however, as you may note, the formula is not providing the appropriate values for the bottom two cells (i.e., the girls whose values should be coming from the bottom table on Sheet2).

Any assistance with this would be greatly appreciated!

[AliGW]

The formula you were given does not reference the lower table - it was taking all values from the boys' table at the top (run it through the Evaluate Formulas feature to see it in action).

Try this:

=INDEX(Sheet2!$N$1:$N$43,MATCH(C2,OFFSET(Sheet2!$A$1,IF(A2=1,0,22),(B2-1),20),1))