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)

 

[Peter_SSs]

You're a genius peter it's perfect,

Hopefully it still works when you have given it a thorough workout.

.. how does the logic behind it work.

The SUMIFS creates an array of the Math scores for each ID that appears in the bottom table and in the order the IDs appear in that bottom table. That is {80;71;60}
There is then a division by True/False depending on whether the ages in the bottom table are > 15 or not. So we get
{80;71;60}/{TRUE;FALSE;TRUE}
That produces
{80;#DIV/0!;60}
The AGGREGATE then takes the minimum value from that array, ignoring errors. Hence 60.

If you want to see the steps, select the formula cells then on the 'Formulas' ribbon tab click 'Evaluate Formula'. What is about to be evaluated is underlined. Click Evaluate to see the result of that part evaluated. Repeat ..

 

[johnsnider]

Hopefully it still works when you have given it a thorough workout.


The SUMIFS creates an array of the Math scores for each ID that appears in the bottom table and in the order the IDs appear in that bottom table. That is {80;71;60}
There is then a division by True/False depending on whether the ages in the bottom table are > 15 or not. So we get
{80;71;60}/{TRUE;FALSE;TRUE}
That produces
{80;#DIV/0!;60}
The AGGREGATE then takes the minimum value from that array, ignoring errors. Hence 60.

If you want to see the steps, select the formula cells then on the 'Formulas' ribbon tab click 'Evaluate Formula'. What is about to be evaluated is underlined. Click Evaluate to see the result of that part evaluated. Repeat ..

I gave it a thorough work through the formula is pretty solid, it still works. Also, thank you for the explanation I appreciate it. The ribbon tab tool is also pretty neat, I never knew about it.

 

[Peter_SSs]

I gave it a thorough work through the formula is pretty solid, it still works. Also, thank you for the explanation I appreciate it.

You're welcome.

The ribbon tab tool is also pretty neat, I never knew about it.

Very useful for working out what is going on (or wrong) in a formula. So much so, I keep it handy on my Quick Access Toolbar.