Dynamic Grade Look Up using 2 criteria

[aeweed]

Hi! Hoping for some help - i have a spreadsheet that has different scoring depending - is there a formula that i can use to have it tell me the score based on the subject and the month and be able to let it change dynamically (ie. change the month and grade look up would change) I know how to do the simple grade look up but stumped on this! Attached is a sample of the charts - hopefully i used the XL2BB add on correctly

Thank you so much!

sample_grade report.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Month	Class	Score Range	Grade
2	1	Math	0	F
3	1	Math	64.7	C				Month	3
4	1	Math	65.1	B
5	1	Math	65.8	A				Science	Math	English
6	2	Math	0	F			Score	62	67.1	67.2
7	2	Math	64.8	C			Grade	?	?	?
8	2	Math	65.1	B
9	2	Math	65.9	A
10	3	Math	0	F
11	3	Math	64.4	C
12	3	Math	64.8	B
13	3	Math	65.5	A
14	1	English	0	F
15	1	English	66.7	C
16	1	English	66.9	B
17	1	English	67.4	A
18	2	English	0	F
19	2	English	66.8	C
20	2	English	67	B
21	2	English	67.6	A
22	3	English	0	F
23	3	English	66.8	C
24	3	English	67	B
25	3	English	67.6	A
26	1	Science	0	F
27	1	Science	67.9	C
28	1	Science	68.2	B
29	1	Science	68.9	A
30	2	Science	0	F
31	2	Science	68.4	C
32	2	Science	68.7	B
33	2	Science	69.4	A
34	3	Science	0	F
35	3	Science	68.5	C
36	3	Science	68.8	B
37	3	Science	69.5	A
Sheet1

 

[dreid1011]

Does this look like what you expect?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Month	Class	Score Range	Grade
2	1	Math	0	F
3	1	Math	64.7	C				Month	1
4	1	Math	65.1	B
5	1	Math	65.8	A				Science	Math	English
6	2	Math	0	F			Score	62	67.1	67.2
7	2	Math	64.8	C			Grade	F	A	B
8	2	Math	65.1	B
9	2	Math	65.9	A
10	3	Math	0	F
11	3	Math	64.4	C
12	3	Math	64.8	B
13	3	Math	65.5	A
14	1	English	0	F
15	1	English	66.7	C
16	1	English	66.9	B
17	1	English	67.4	A
18	2	English	0	F
19	2	English	66.8	C
20	2	English	67	B
21	2	English	67.6	A
22	3	English	0	F
23	3	English	66.8	C
24	3	English	67	B
25	3	English	67.6	A
26	1	Science	0	F
27	1	Science	67.9	C
28	1	Science	68.2	B
29	1	Science	68.9	A
30	2	Science	0	F
31	2	Science	68.4	C
32	2	Science	68.7	B
33	2	Science	69.4	A
34	3	Science	0	F
35	3	Science	68.5	C
36	3	Science	68.8	B
37	3	Science	69.5	A
Sheet1

Cell Formulas
Range	Formula
H7:J7	H7	=XLOOKUP(H6,FILTER($C$2:$C$37,($A$2:$A$37=$I$3)*($B$2:$B$37=H$5)),FILTER($D$2:$D$37,($A$2:$A$37=$I$3)*($B$2:$B$37=H$5)),"",-1)

 

[Fluff]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Month	Class	Score Range	Grade
2	1	Math	0	F
3	1	Math	64.7	C				Month	3
4	1	Math	65.1	B
5	1	Math	65.8	A				Science	Math	English
6	2	Math	0	F			Score	62	67.1	67.2
7	2	Math	64.8	C			Grade	F	A	B
8	2	Math	65.1	B
9	2	Math	65.9	A
10	3	Math	0	F
11	3	Math	64.4	C
12	3	Math	64.8	B
13	3	Math	65.5	A
14	1	English	0	F
15	1	English	66.7	C
16	1	English	66.9	B
17	1	English	67.4	A
18	2	English	0	F
19	2	English	66.8	C
20	2	English	67	B
21	2	English	67.6	A
22	3	English	0	F
23	3	English	66.8	C
24	3	English	67	B
25	3	English	67.6	A
26	1	Science	0	F
27	1	Science	67.9	C
28	1	Science	68.2	B
29	1	Science	68.9	A
30	2	Science	0	F
31	2	Science	68.4	C
32	2	Science	68.7	B
33	2	Science	69.4	A
34	3	Science	0	F
35	3	Science	68.5	C
36	3	Science	68.8	B
37	3	Science	69.5	A
Data

Cell Formulas
Range	Formula
H7:J7	H7	=TAKE(FILTER($D$2:$D$100,($A$2:$A$100=$I$3)*($B$2:$B$100=H5)*($C$2:$C$100<=H6)),-1)

 

[aeweed]

Does this look like what you expect?

Book1
	A	B	C	D	E	F	G	H	I	J
1	Month	Class	Score Range	Grade
2	1	Math	0	F
3	1	Math	64.7	C				Month	1
4	1	Math	65.1	B
5	1	Math	65.8	A				Science	Math	English
6	2	Math	0	F			Score	62	67.1	67.2
7	2	Math	64.8	C			Grade	F	A	B
8	2	Math	65.1	B
9	2	Math	65.9	A
10	3	Math	0	F
11	3	Math	64.4	C
12	3	Math	64.8	B
13	3	Math	65.5	A
14	1	English	0	F
15	1	English	66.7	C
16	1	English	66.9	B
17	1	English	67.4	A
18	2	English	0	F
19	2	English	66.8	C
20	2	English	67	B
21	2	English	67.6	A
22	3	English	0	F
23	3	English	66.8	C
24	3	English	67	B
25	3	English	67.6	A
26	1	Science	0	F
27	1	Science	67.9	C
28	1	Science	68.2	B
29	1	Science	68.9	A
30	2	Science	0	F
31	2	Science	68.4	C
32	2	Science	68.7	B
33	2	Science	69.4	A
34	3	Science	0	F
35	3	Science	68.5	C
36	3	Science	68.8	B
37	3	Science	69.5	A
Sheet1

Cell Formulas
Range	Formula
H7:J7	H7	=XLOOKUP(H6,FILTER($C$2:$C$37,($A$2:$A$37=$I$3)*($B$2:$B$37=H$5)),FILTER($D$2:$D$37,($A$2:$A$37=$I$3)*($B$2:$B$37=H$5)),"",-1)

this worked great! thanks for your help and quick reply! i need to freshen up on xlookup! I always do index match match!

 

[aeweed]

Hi & welcome to MrExcel.
Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Month	Class	Score Range	Grade
2	1	Math	0	F
3	1	Math	64.7	C				Month	3
4	1	Math	65.1	B
5	1	Math	65.8	A				Science	Math	English
6	2	Math	0	F			Score	62	67.1	67.2
7	2	Math	64.8	C			Grade	F	A	B
8	2	Math	65.1	B
9	2	Math	65.9	A
10	3	Math	0	F
11	3	Math	64.4	C
12	3	Math	64.8	B
13	3	Math	65.5	A
14	1	English	0	F
15	1	English	66.7	C
16	1	English	66.9	B
17	1	English	67.4	A
18	2	English	0	F
19	2	English	66.8	C
20	2	English	67	B
21	2	English	67.6	A
22	3	English	0	F
23	3	English	66.8	C
24	3	English	67	B
25	3	English	67.6	A
26	1	Science	0	F
27	1	Science	67.9	C
28	1	Science	68.2	B
29	1	Science	68.9	A
30	2	Science	0	F
31	2	Science	68.4	C
32	2	Science	68.7	B
33	2	Science	69.4	A
34	3	Science	0	F
35	3	Science	68.5	C
36	3	Science	68.8	B
37	3	Science	69.5	A
Data

Cell Formulas
Range	Formula
H7:J7	H7	=TAKE(FILTER($D$2:$D$100,($A$2:$A$100=$I$3)*($B$2:$B$100=H5)*($C$2:$C$100<=H6)),-1)

Thank you!

 

[Fluff]

Glad we could help & thanks for the feedback.