Having excel choose a specific cell in a table based on two columns

[brianu]

I am trying to create a spreadsheet to calculate GPA for a high school. If I have the type of course and a grade, example, honors with a grade of a A. What formula can I use to pinpoint where this falls in a grid.
If you need any additional information please let me know. I would like the quality points for this particular scenario to be displayed in K41. In this case the quality points that should be displayed is 6.0. Thank you. Here is my data:

								Course Difficulty	Grade	Quality Points Earned
Final Avg	Letter Grade	Quality Points				Scale for		Honors	A
		AP	Honors	Academic	Elective	Unweighted GPA		AP	B
100-98	A+	7.3	6.3	5.3	4.3	4.3		Academic	A+
97-94	A; O; P	7.0	6.0	5.0	4.0	4.0		Elective	B+
93-92	A-	6.7	5.7	4.7	3.7	3.7
91-89	B+	6.3	5.3	4.3	3.3	3.3
88-85	B; S	6.0	5.0	4.0	3.0	3.0
84-83	B-	5.7	4.7	3.7	2.7	2.7
82-80	C+	5.3	4.3	3.3	2.3	2.3
79-76	C	5.0	4.0	3.0	2.0	2.0
75-74	C-	4.7	3.7	2.7	1.7	1.7
73-71	D+	4.3	3.3	2.3	1.3	1.3
70-67	D	4.0	3.0	2.0	1.0	1.0
66-65	D-	3.7	2.7	1.7	0.7	0.7
64-50	F	0.0	0.0	0.0	0.0	0.0
49-0	X; U	0.0	0.0	0.0	0.0	0.0

<colgroup><col span="4"><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[exceldevs]

Hi brianu, try this.

	A	B	C	D	E	F	G	H	I	J	K
40									Course Difficulty	Grade	Quality Points Earned
41	Final Avg	Letter Grade	Quality Points				Scale for		Honors	A	6.0
42			AP	Honors	Academic	Elective	Unweighted GPA		AP	B	6.0
43	100-98	A+	7.3	6.3	5.3	4.3	4.3		Academic	A+	5.3
44	97-94	A; O; P	7	6	5	4	4		Elective	B+	3.3
45	93-92	A-	6.7	5.7	4.7	3.7	3.7
46	91-89	B+	6.3	5.3	4.3	3.3	3.3
47	88-85	B; S	6	5	4	3	3
48	84-83	B-	5.7	4.7	3.7	2.7	2.7
49	82-80	C+	5.3	4.3	3.3	2.3	2.3
50	79-76	C	5	4	3	2	2
51	75-74	C-	4.7	3.7	2.7	1.7	1.7
52	73-71	D+	4.3	3.3	2.3	1.3	1.3
53	70-67	D	4	3	2	1	1
54	66-65	D-	3.7	2.7	1.7	0.7	0.7
55	64-50	F	0	0	0	0	0
56	49-0	X; U	0	0	0	0	0

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula K41 {=INDEX($C$43:$G$56,IFERROR(MATCH($J41,$B$43:$B$56,0),SUM((ROW($B$43:$B$56)-MIN(ROW($B$43:$B$56))+1)*IF(ISNUMBER(SEARCH($J41,$B$43:$B$56)),1)*IF(ISNUMBER(SEARCH(";",$B$43:$B$56)),1))),MATCH($I41,$C$42:$G$42,0))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

 

[brianu]

Exceldevs,
Thank you so much! Worked like a charm. I am quite impressed. Have a great day.

Brian

 

[exceldevs]

Hi Brian,

You're welcome, thanks for the feedback.

 

[brianu]

Question for you exceldevs, or anyone that comes across this. The data I import will not have the course difficulty level instead it will have something like AP English or Physics Hon is there something I could do to the formula you gave me before for it to search for AP or Hon in order to determine the difficulty of the course and which quality points column to use. Thank you again as you have been tremendously helpful.

Brian

 

[exceldevs]

Hi Brian,

The formula was constructed in a flexible/extensible way that as long as you have matches between column I and C42:G42 it shall work.