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"
[TABLE="width: 274"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Learning[/TD]
[TD]26[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Memory[/TD]
[TD]17[/TD]
[/TR]
</tbody>[/TABLE]


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