LARGE IF AND Formula

[ferr]

Looking to populate the ID with the largest nth Value in L15 :P15 based on the following conditions.

Date is 10/17
location is North

=INDEX(A:A,MATCH(LARGE(IF(AND(D:D=L13,B:B=K15),C:C),1),C:C,0))
formula does not work, looking for the appropriate formula

Copy of Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	ID	Location	Value	Date
2	CA-01	North	$ 6,940.00	10-Oct
3	CA-02	North	$ 466.00	17-Oct
4	CA-03	South	$ 46,464.00	10-Oct
5	CA-04	North	$ 335,647.00	10-Oct
6	CA-05	South	$ 799,930.00	17-Oct
7	CA-06	South	$ 30,506.00	10-Oct
8	CA-07	South	$ 746,435.00	10-Oct
9	CA-08	South	$ 56,777.00	10-Oct
10	CA-09	South	$ 503.00	17-Oct
11	CA-10	South	$ 353,567.00	10-Oct
12	CA-11	North	$ 474,367.00	17-Oct
13	CA-12	North	$ 5,754,332.00	10-Oct								17-Oct
14	CA-13	South	$ 6,267.00	17-Oct								1	2	3	4	5
15	CA-14	North	$ 353,577.00	17-Oct							North	#N/A	#NUM!	#NUM!	#NUM!	#NUM!
16	CA-15	South	$ 10,292.00	10-Oct							South
17	CA-16	North	$ 2,030,405.00	10-Oct
18	CA-17	South	$ 24,677,788.00	10-Oct
19	CA-18	North	$ 35,353,536.00	17-Oct
20	CA-19	South	$ 464.00	18-Oct
21	CA-20	North	$ 2,403,030.00	10-Oct
22	CA-21	South	$ 30,405.00	11-Oct
23	CA-22	South	$ 30,530.00	10-Oct
24	CA-23	South	$ 22,020.00	10-Oct
25	CA-24	North	$ 10,202.00	10-Oct
26	CA-25	North	$ 102,045.00	17-Oct
27	CA-26	South	$ 35,040,406.00	17-Oct
28	CA-27	North	$ 30,340.00	17-Oct
29	CA-28	South	$ 34,030.00	17-Oct
30	CA-29	North	$ 450,606.00	10-Oct
31	CA-30	South	$ 4.00	17-Oct
32	CA-31	North	$ 4,357.00	18-Oct
33	CA-32	South	$ 867,564.00	17-Oct
34	CA-33	North	$ 4,657.00	9-Oct
35	CA-34	North	$ 243,546.00	10-Oct
36	CA-35	North	$ 87,586.00	10-Oct
37	CA-36	South	$ 689,965.00	11-Oct
38	CA-37	North	$ 4,656,588.00	10-Oct
39	CA-38	South	$ 578,585.00	11-Oct
40	CA-39	South	$ 5,754,342.00	17-Oct
Sheet1

Cell Formulas
Range	Formula
L15:P15	L15	=INDEX($A:$A,MATCH(LARGE(IF(AND($D:$D=$L$13,$B:$B=$K$15),$C:$C),L14),$C:$C,0))

Named Ranges
Name	Refers To	Cells
_FilterDatabase	=Sheet1!$A$1:$D$44	L15:P15

 

[StephenCrump]

The AND() will collapse your two vectors to a single value (unless you apply it BYROW).

Try L15: =INDEX($A:$A,MATCH(LARGE(IF(($D:$D=$L$13)*($B:$B=$K15),$C:$C),L$14),$C:$C,0))

Or you could replace L15: P15 with L15:=TRANSPOSE(TAKE(SORT(FILTER(A$2:C$40,(B$2:B$40=K15)*(D$2:D$40=L$13)),3,-1),5,1))

 

[ferr]

Thank you