Find a value in a specific column based on two values/criteria from multiple columns? (match/index/lookup)

[perola.rike]

I have 3 columns with Age in the top row (11, 12, 13 years), in the rows below I have the possible results from a test (1-6, 2-7 and 3-8). The far right column contains the grades that correspond to the results for each age group/column.

I need a formula that based on AGE and RESULT finds the GRADE
If a 11 year old get the result of 1, the grade will be an A.
I have tried for many hours with all sorts of match/index/lookup formulas with no luck...I can't put the loose formulas into a functional one that get the grades based on age/result...

Any suggestions?

age	11	12	13	grade
	1	2	3	a
	2	3	4	b
	3	4	5	c
	4	5	6	d
	5	6	7	e
	6	7	8	f

<tbody>
</tbody>

 

[AhoyNC]

Formula in D10

=INDEX($E$2:$E$7,MATCH($C$10,INDEX($B$2:$D$7,0,MATCH($B$10,$B$1:$D$1,0)),0))

	A	B	C	D	E
1	age	11	12	13	grade
2		1	2	3	a
3		2	3	4	b
4		3	4	5	c
5		4	5	6	d
6		5	6	7	e
7		6	7	8	f
8
9		Age	Result	Grade
10		12	4	c

<tbody>
</tbody>

 

[perola.rike]

Fantastic, it works perfectly, thank you!

 

[perola.rike]

One additional question:
I will use this formula for other tables/matrixes where the age variable are ranges (see table below), not specific values. Any tips how to get the formula to work on ranges (not exact match). I've tried to modify the formula from this:
=INDEX($E$2:$E$7,MATCH($C$10,INDEX($B$2:$D$7,0,MATCH($B$10,$B$1:$D$1,0)),0))


to this:

=INDEX($E$2:$E$7,MATCH($C$10,INDEX($B$2:$D$7,0,MATCH($B$10,$B$1:$D$1,1)),0))
but it wont work...

	A	B	C	D	E
1	age	10-15	16-20	21-25	grade
2		1	2	3	a
3		2	3	4	b
4		3	4	5	c
5		4	5	6	d
6		5	6	7	e
7		6	7	8	f
8
9		Age	Result	Grade
10		12	4	c

<tbody>
</tbody>

 

[AhoyNC]

You could add another cell and use a VLOOKUP to find the age range for the MATCH, or if you don't want to add another cell you could put the VLOOKUP into the MATCH formula (see cell E11). I hard coded the VLOOKUP table into the formula, but if you would be adding or changing age ranges it would probably be better to to set up a table somewhere in your workbook that could be easily changed.

Excel Workbook
	A	B	C	D	E
1	age	10-15	16-20	21-25	grade
2	*	1	2	3	a
3	*	2	3	4	b
4	*	3	4	5	c
5	*	4	5	6	d
6	*	5	6	7	e
7	*	6	7	8	f
8	*	*	*	*	*
9	*	Age	Age Range	Result	Grade
10	*	18	16-20	4	c
11	*	*	*	*	c
Sheet

 

[perola.rike]

Wow, thanks! I'll modify your suggestions to fit my use. My tables are larger Grades/age example, and your formulas will certainly make it quite easy to apply this to matrixes with many columns and rows. And easy to maintain!

 

[AhoyNC]

You're welcome. Thanks for the feedback.