combining Index Match Match with IFS statements

[drewab11]

Hello All,

I have some normative data (what I call Index Scores) that are split by age groups (by decade). The "normal index" score is found by looking up two numbers in a table. I have managed to accomplish this with

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545}</style>=INDEX(Imm!B47:Z87,MATCH(ScoreSheet!B8,Imm!A47:A87,0),MATCH(ScoreSheet!B9,Imm!B46:Z46,0))

However, I need the table that is referenced to be based on the age of the individual. For example, if the individual is between 40-49 the index score is based on Table1, but if their age is 50-59 it should be based on Table2.

I cannot seem to combine the Index Match formulate with an IF statement. Any help would be appreciated.

Numbers Used "Index Score"

Learning	26	100
Memory	17

<colgroup><col><col><col></colgroup><tbody>
</tbody>

But the Index Score would be different for a 45 year old compared to a 55 year old.

 

[etaf]

what table for 0-39 and what table for 60-xxx

what are the tables you want to use

these in your example are for what age group
Imm!B47:Z87
Imm!A47:A87
Imm!B46:Z46

=IF( AND( cell with age >= 40, cell with age <= 49), table for true - ie full formulas , IF( AND( cell with age >= 50, cell with age <= 59), table for true - ie full formulas , "not in age group))

 

[drewab11]

Hi Wayne,

Thank you! Even with the little information I provided (sorry about that!), your solution works perfectly! In the example that was for age group 40-49. So I changed up the Index Match formula for the 50-59 age groups and it appears to work! Thanks again, I very much appreciate it. If you have any suggestions on a simpler process, I'd appreciate it. I have tables for 20-39, 40-49, 50-59, 60-69, 70-79, 80-89... so these will be long formulas. But it sure beats having to look up in a reference table every single time.

-Andrew