Index the value with the criteria and get the lowest with in the range

[mart_mrexcel]

Id like to get the Position where it matches the criteria of Division Code, Subdivision, and Level with the lowest grade. In the source table below, I'm expecting to get the result of Position1 ((D2) where the grade is 11 compared to the other where grades are 99 & 15

	A	B	C	D	E
1	Division Code	Sub Division Code	Level	result
2	50080210	50080220	L1	Position1
3
4	Position	Division Code	Sub Division Code	Level	Grade
5	Position1	50080210	50080220	L1	11
6	Position4	50080210	50080220	L1	99
7	Position5	50091753	50092471	L2	10
8	Position6	50080210	50080220	L1	15
9

 

[myall_blues]

Here's one way, but note that it will fail if the minimum grade that matches all criteria is not unique.

Book1
	A	B	C	D	E	F
1	A	B	C	D	E
2	1	Division Code	Sub Division Code	Level	result
3	2	50080210	50080220	L1	Position1
4	3
5	4	Position	Division Code	Sub Division Code	Level	Grade
6	5	Position1	50080210	50080220	L1	11
7	6	Position4	50080210	50080220	L1	99
8	7	Position5	50091753	50092471	L2	10
9	8	Position6	50080210	50080220	L1	15
Sheet2

Cell Formulas
Range	Formula
E3	E3	=INDEX(B6:F9,MATCH(MINIFS(F6:F9,C6:C9,B3,D6:D9,C3,E6:E9,D3),F6:F9,0),1)

 

[mart_mrexcel]

Here's one way, but note that it will fail if the minimum grade that matches all criteria is not unique.

Book1
	A	B	C	D	E	F
1	A	B	C	D	E
2	1	Division Code	Sub Division Code	Level	result
3	2	50080210	50080220	L1	Position1
4	3
5	4	Position	Division Code	Sub Division Code	Level	Grade
6	5	Position1	50080210	50080220	L1	11
7	6	Position4	50080210	50080220	L1	99
8	7	Position5	50091753	50092471	L2	10
9	8	Position6	50080210	50080220	L1	15
Sheet2

Cell Formulas
Range	Formula
E3	E3	=INDEX(B6:F9,MATCH(MINIFS(F6:F9,C6:C9,B3,D6:D9,C3,E6:E9,D3),F6:F9,0),1)

Thank you Myall_Blues, your right