Conditional array search

[ehcpieterse]

Hi,

I have been able to use an array function to search for a conditional max value:

{=MAX(IF(MyRange=TestValue,LookupRange))}

Given the data below (apologies for the formatting) how do I return the corresponding value in the 3rd column (AvgPx), given that I used the formula above to return the conditional max in the 2nd column (CumQty)?


OrderID CumQty AvgPx
1,FixClient,6394807088963162 0 0
2,FixClient,6394807122184812 0 0
1,FixClient,6394807088963162 0 0
2,FixClient,6394807122184812 0 0
2,FixClient,6394807122184812 2201 3407
1,FixClient,6394807088963162 201 4387
2,FixClient,6394807122184812 2206 3406.959202
1,FixClient,6394807088963162 206 4362.776699
2,FixClient,6394807122184812 2229 3406.77389
1,FixClient,6394807088963162 229 4264.973799

 

[Andrew Poulsom]

One way:

Excel 2010
	A	B	C	D	E	F
1	OrderID	CumQty	AvgPx		2,FixClient,6394807122184812	3406.77389
2	1,FixClient,6394807088963162	0	0		1,FixClient,6394807088963162	4264.973799
3	2,FixClient,6394807122184812	0	0
4	1,FixClient,6394807088963162	0	0
5	2,FixClient,6394807122184812	0	0
6	2,FixClient,6394807122184812	2201	3407
7	1,FixClient,6394807088963162	201	4387
8	2,FixClient,6394807122184812	2206	3406.959202
9	1,FixClient,6394807088963162	206	4362.776699
10	2,FixClient,6394807122184812	2229	3406.77389
11	1,FixClient,6394807088963162	229	4264.973799
Sheet1

Cell Formulas
Range	Formula
F1	=INDEX(C$2:C$11,MATCH(MAX(INDEX((A$2:A$11=E1)*(B$2:B$11),)),INDEX((A$2:A$11=E1)*(B$2:B$11),),FALSE))

 

[ehcpieterse]

Great thanks! Will give it a try.