INDEX MATCH multiple criteria greater than or equal to

[johnsnider]

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Subject[/TD]
[TD]score[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Math[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]English[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Math[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]English[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]English[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]Chem[/TD]
[TD]54[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]Math[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]Chem[/TD]
[TD]83[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Age[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]matt[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]john[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]simon[/TD]
[TD]16[/TD]
[/TR]
</tbody>[/TABLE]


This solution needs to be a formula that must be within one cell and needs to be dynamic(e.g changes).

Q1
What is the lowest score received in Math for students above the age of 15?

Q2
Return the 2nd letter for the name of a student with the lowest average score?


So Far I tried using something like this however it fails =INDEX(A2:C9,MATCH(MIN(IF(AND(B2:B11="MATH",A11:A13=IF(C11:C13>15,A11:A13),C2:C9)),A2:A11,0),2)

 

[AhoyNC]

Maybe something like one of these.
The formula in C18 needs Excel 2016 or 365.
The formula in C19 will work in any ver.

These are array formulas and must be entered with

Excel Workbook
	A	B	C
1	ID	Subject	score
2	1000	Math	80
3	1001	English	45
4	1002	Math	60
5	1002	English	87
6	1001	English	50
7	1000	Chem	54
8	1001	Math	71
9	1002	Chem	83
10
11
12
13	ID	Name	Age
14	1000	matt	12
15	1001	john	15
16	1002	simon	16
17
18			60
19			60
CTRL-SHIFT-ENTER.

 

[johnsnider]

AhoyNC thank you for the help, much appreciated your formula worked beautifully. Is there any chance you could explain to me the logic behind it, I am a bit confused about how it works. Also, did you know way to solve Q2. So far I have this =MIN(AVERAGEIF(A2:A11,A14:A16,C2:C1)) which gives me the lowest average but doesn't leave a reference for me to find the ID.

 

[Peter_SSs]

AhoyNC thank you for the help, much appreciated your formula worked beautifully.

Are you sure?

In my sample data below I have changed matt's age. Shouldn't the answer still be 60, not 80?

Excel Workbook
	A	B	C
1	ID	Subject	score
2	1000	Math	80
3	1001	English	45
4	1002	Math	60
5	1002	English	87
6	1001	English	50
7	1000	Chem	54
8	1001	Math	71
9	1002	Chem	83
10
11
12
13	ID	Name	Age
14	1000	matt	18
15	1001	john	15
16	1002	simon	16
17
18			80
19			80
Lowest

 

[johnsnider]

Hey, Peter thank you for checking, you're right. The method only finds the first person above 15. By any chance would you know a method to fix this.

 

[Peter_SSs]

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function MinScore(rScores As Range, rAges As Range, sSubject As String, lAgeLimit As Long) As Variant
  Dim d As Object
  Dim aScores As Variant, aAges As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  aScores = rScores.Value
  aAges = rAges.Value
  For i = 1 To UBound(aAges)
    d(aAges(i, 1)) = aAges(i, 3)
  Next i
  sSubject = LCase(sSubject)
  MinScore = "N/A"
  For i = 1 To UBound(aScores)
    If LCase(aScores(i, 2)) = sSubject Then
      If d(aScores(i, 1)) > lAgeLimit Then
        If aScores(i, 3) < MinScore Then MinScore = aScores(i, 3)
      End If
    End If
  Next i
End Function

Excel Workbook
	A	B	C
1	ID	Subject	score
2	1000	Math	80
3	1001	English	45
4	1002	Math	60
5	1002	English	87
6	1001	English	50
7	1000	Chem	54
8	1001	Math	71
9	1002	Chem	83
10
11
12
13	ID	Name	Age
14	1000	matt	18
15	1001	john	15
16	1002	simon	16
17
18			60
Lowest

 

[johnsnider]

Thank you for the solution, much appreciated it worked but is it possible for there to be a solution that doesn't use a macro-enabled workbook. It has to work just using the default Excel formulas.

 

[Peter_SSs]

.. is it possible for there to be a solution that doesn't use a macro-enabled workbook.

Quite possibly, but it hasn't come to me yet otherwise I would have posted it.

 

[Peter_SSs]

Assuming each student has at most one score for each subject, try this. (I haven't tested heavily)

Excel Workbook
	A	B	C
1	ID	Subject	score
2	1000	Math	80
3	1001	English	45
4	1002	Math	60
5	1002	English	87
6	1001	English	50
7	1000	Chem	54
8	1001	Math	71
9	1002	Chem	83
10
11
12
13	ID	Name	Age
14	1000	matt	18
15	1001	john	15
16	1002	simon	16
17
18			60
Lowest

 

[johnsnider]

You're a genius peter it's perfect, how does the logic behind it work.