Retrieving cell reference for value below threshold in conditional list

[drquesh]

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.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Dept[/TD]
[TD]Name[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD]Dept[/TD]
[TD]Threshold[/TD]
[TD]Person[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]Mike[/TD]
[TD]5.6[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]6.2[/TD]
[TD]Formula = Mike[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]Sarah[/TD]
[TD]8.5[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2.2[/TD]
[TD]Formula = Sandra[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]A[/TD]
[TD]Lindsey[/TD]
[TD]3.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]Catherine[/TD]
[TD]4.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]Sandra[/TD]
[TD]2.2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A[/TD]
[TD]Paul[/TD]
[TD]6.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]Geoff[/TD]
[TD]3.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G
1	Dept	Name	Value		Dept	Threshold	Person
2	A	Mike	5.6		A	6.2	Mike
3	B	Saraj	8.5		B	2.2	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
Sheet1

Cell Formulas
Range	Formula
G2	{=INDEX($B$2:$B$8,MATCH(MAX(IF($A$2:$A$8=$E2,IF($C$2:$C$8<=$F2,$C$2:$C$8))),$C$2:$C$8,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr][tr][td]

1​

[/td][td]Dept[/td][td]Name[/td][td]Value[/td][td][/td][td]Dept[/td][td]Threshold[/td][td]Person[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]A[/td][td]Mike[/td][td]

5.6​

[/td][td][/td][td]A[/td][td]

6.2​

[/td][td]Mike[/td][td]Chris[/td][/tr]
[tr][td]

3​

[/td][td]B[/td][td]Sarah[/td][td]

8.5​

[/td][td][/td][td]B[/td][td]

2.2​

[/td][td]Sandra[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]A[/td][td]Lindsey[/td][td]

3.5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td]A[/td][td]Chris[/td][td]

5.6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

6​

[/td][td]B[/td][td]Catherine[/td][td]

4.7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td]B[/td][td]Sandra[/td][td]

2.2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]A[/td][td]Paul[/td][td]

6.4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td]B[/td][td]Geoff[/td][td]

3.3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

In G2 control+shift+enter, not just enter, copy across as far as needed, and down:

=IFERROR(INDEX($B$2:$B$9,SMALL(IF($A$2:$A$9=$E2,IF($C$2:$C$9=MAX(IF($A$2:$A$9=$E2,IF($C$2:$C$9<=$F2,$C$2:$C$9))),
       ROW($B$2:$B$9)-ROW($B$2)+1)),COLUMNS($G2:G2))),"")

 

[drquesh]

Thank you both. Knew I had the right functions, but just couldn't crack the recipe. Really appreciate the help.

 

[Aladin Akyurek]

Thank you both. Knew I had the right functions, but just couldn't crack the recipe. Really appreciate the help.

You need to evaluate the proposals with care. The first proposal will not only miss ties, but it will also fail under adverse conditions (e.g., hange C3 to 2.2).

 

[wideboydixon]

You need to evaluate the proposals with care. The first proposal will not only miss ties, but it will also fail under adverse conditions (e.g., hange C3 to 2.2).

From the original post:

For duplicates, I'm happy to just return the first result.

The solution provided does exactly as requested. The OP did not request all values in case of a tie. There's also no suggestion that there are no values that will match which I assume is the "adverse conditions". If that's the case then:

=IFERROR(INDEX($B$2:$B$8,MATCH(MAX(IF($A$2:$A$8=$E2,IF($C$2:$C$8<=$F2,$C$2:$C$8))),$C$2:$C$8,0)),"")

Entered as an array formula.

WBD