Advanced lookup help?

[usdaddyrose3]

I need help. I want to make my formulas more effective for the following situation. There are 2 different types of class, Separate or Combined, with 2 tiers per class, Higher or Foundation. When they take their tests, there are 4 different tests with different grade boundaries. I need to create a sheet for the teacher to input the class, marks, and tier, and have it return the grade. Currently, I have the following nested if statement which works, but I was hoping for a more efficient way of doing this? Is there a way of using Index and Match?

=IF(ISBLANK(K5),
"",
IF(ISNUMBER(SEARCH("s",I5)),
IF(J5="H",
VLOOKUP(K5,B5:F14,5,TRUE),
VLOOKUP(K5,C5:F14,4,TRUE)),
IF(J5="H",
VLOOKUP(K5,D5:F14,3,TRUE),
VLOOKUP(K5,E5:F14,2,TRUE))))

AQA Master Boundaries.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
2	Biology	Paper 1						Inputs				DesiredOutput	Current Formula
3	S_Higher	S_Foundation	C_Higher	C_Foundation	Grade		Student	Class	Tier	Mark		Grade
4	0	0	0	0	U		A	Separate	H	34		4	4
5		10		6	1		B	Separate	F	34		2	2
6		25		14	2		C	Combined	H	34		7	7
7	23	40	13	23	3		D	Combined	F	34		4	4
8	28	56	16	32	4
9	36	64	22	39	5
10	44		27		6
11	53		32		7
12	60		38		8
13	67		45		9
Sheet1

Cell Formulas
Range	Formula
N4:N7	N4	=IF(ISBLANK(K4),"",IF(ISNUMBER(SEARCH("s",I4)),IF(J4="H", VLOOKUP(K4,B4:F13,5,TRUE),VLOOKUP(K4,C4:F13,4,TRUE)),IF(J4="H",VLOOKUP(K4,D4:F13,3,TRUE),VLOOKUP(K4,E4:F13,2,TRUE))))

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(K4="","",INDEX($F$4:$F$13,MATCH(K4,INDEX($B$4:$E$13,,MATCH(LEFT(I4)&"_"&J4&"*",$B$3:$E$3,0)),1)))