Would be grateful for help adding a conditional filter to an array formula to find the maximum value up to a variable threshold in an unsorted list. I can do it for a whole list using help in other posts, but not for a conditional subset.
Example shown below. For each department, I want to find the name of the individual whose value is closest to or equal to, but not over, to a specific value for their department.
So, for department A with a threshold of 6.2, from the list of 5.6, 3.5 and 6.4, it returns row 2's name, Mike. I need the formula to give the results in cells G2 and G3.
<tbody>
</tbody>
I know this is likely to need an array formula using MATCH and INDEX, and probably MIN/MAXs given it is unsorted, but I haven't managed to add a conditional filter to the examples I have found that will work on a whole unsorted list.
For duplicates, I'm happy to just return the first result.
Thanks in advance for your help
Chris
[/HTML][/HTML]
Example shown below. For each department, I want to find the name of the individual whose value is closest to or equal to, but not over, to a specific value for their department.
So, for department A with a threshold of 6.2, from the list of 5.6, 3.5 and 6.4, it returns row 2's name, Mike. I need the formula to give the results in cells G2 and G3.
A | B | C | D | E | F | G | |
1 | Dept | Name | Value | Dept | Threshold | Person | |
2 | A | Mike | 5.6 | A | 6.2 | Formula = Mike | |
3 | B | Sarah | 8.5 | B | 2.2 | Formula = Sandra | |
4 | A | Lindsey | 3.5 | ||||
5 | B | Catherine | 4.7 | ||||
6 | B | Sandra | 2.2 | ||||
7 | A | Paul | 6.4 | ||||
8 | B | Geoff | 3.3 |
<tbody>
</tbody>
I know this is likely to need an array formula using MATCH and INDEX, and probably MIN/MAXs given it is unsorted, but I haven't managed to add a conditional filter to the examples I have found that will work on a whole unsorted list.
For duplicates, I'm happy to just return the first result.
Thanks in advance for your help
Chris
[/HTML][/HTML]