median with multiple criteria and value ranges

[nmccall]

I have a report, posted below, that gives different information about students. Here is a sample.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Fund Name	Status	Actual Amt	Data Valid	Dependency Status	Tfc	Fund Family 2	Tot Aid	Residentcy	Student Type	Housing
2	PELL GRANT	A	3200	Y	D	2302	NPC Grant	12015	R	F	1
3	DL SUB	A	4500	Y	D	2302	NPC Loan	12015	R	F	1
4	ACG Grant	A	650	Y	D	2302	NPC Grant	12015	R	F	1
5	FWS	A	1500	Y	D	2302	NPC Loan	12015	R	F	1
6	FSEOG	A	600	Y	D	0	NPC Grant	13648	R	F	1
7	PELL GRANT	A	5550	Y	D	0	NPC Grant	13648	R	F	1
8	DL SUB	A	298	Y	D	0	NPC Loan	13648	R	F	1
9	ACG Grant	A	750	Y	D	0	NPC Grant	13648	R	F	1
10	FWS	A	1500	Y	D	0	NPC Loan	13648	R	F	1
11	PELL GRANT	A	5550	Y	I	0	NPC Grant	12660	R	F	3
12	FWS	A	3000	Y	I	0	NPC Loan	12660	R	F	3
13	DL SUB	A	3500	Y	I	5606	NPC Loan	9500	R	X	3
14	PELL GRANT	A	3800	Y	D	1741	NPC Grant	10970	R	F	1
15	FWS	A	1500	Y	D	1741	NPC Loan	10970	R	F	1
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H
18	EFC Low Value	EFC High Value	Awards for Dependent On Campus
19			Loan Low	Loan High	Loan Median	Grant Low	Grant High	Grant Median
20	0	0	8	5500	3168	0	5550	2775
21	1	500
22	501	1000
23	1001	1500
24	1501	2000
25	2001	2500
Excel 2010 Using this information, I want to create a table that gives me the loan low, high, and median, as well as the grant low, high, and median values based on other criteria. Sheet1

Excel 2010



The two columns on the left correspond to column F in the original data. A20 and B20 give a range from 0-0 for the efc. A21 and B21 give a range from 1-500 for the efc. For the first row I used

=min(if(h2:h2429="NPC Loan",if(f2:f2429=0,if(k2:k2429=1,if(e2:e2429="D",c2:c2429)))))
ctrl+shift+enter

This gives me the low loan amount for awards for dependent on campus found in cell C20.

I completed the first row making slight adjustments based on what my criteria were.

Now I need to do this same thing, but for a range on my efc(tfc in original data). I am at a loss on how to this.