Find and adjacent cell with criteria

Question

I have the following spreadsheet:

pserial	sex	marstatb	age
10105101	1	5	92
10115101	1	1	63
10120101	2	4	56
10127101	1	2	38
10127102	2	2	25
10135101	2	1	37
10135102	2	-1	14
10135103	2	-1	10
10137101	1	2	35
10137102	2	2	29

Where pserial: Serial number of individual Sex: male/female marstatb: Marital status including cohabitees

I know that the person is divorced if marstatb= 4, and that the person is female if sex=2

I need to find the pserial value for the oldest divorced female.

I was thinking of using the filtering function, IF function for the criterias but I'm not sure how. I think the INDEX function would be useful for this too.

UPDATE: This is my function so far:

=@FILTER(A2:A9282,MAXIFS(D2:D9282,B2:B9282,2,C2:C9282,4)=D2:D9282)

Answer 1

Using FILTER() & MAXIFS()

---

• Formula used in cell F2

=FILTER($A$2:$D$12,(MAXIFS(D2:D12,B2:B12,2,C2:C12,4)=D2:D12)*(B2:B12=2)*(C2:C12=4))

---

Another alternative is using INDEX() & AGGREGATE()

---

• Formula used in cell F4

=INDEX(A2:D12,AGGREGATE(15,7,ROW(A2:D12)/
(D2:D12=AGGREGATE(14,7,D2:D12/((B2:B12=2)*(C2:C12=4)),
ROW($ZZ1))*(B2:B12=2)*(C2:C12=4)),1)-1,0)

---

Using SORT() & TAKE()

---

• Formula used in cell F6

=LET(x,SORT(A2:D12,4,-1),
TAKE(FILTER(x,(INDEX(x,,2)=2)*(INDEX(x,,3)=4)),1))

---

With Headers:

=LET(x,SORT(A1:D12,4,-1),VSTACK(TAKE(x,1),
TAKE(FILTER(x,(INDEX(x,,2)=2)*(INDEX(x,,3)=4)),1)))