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)`
Jan 17, 2023 in Others 292 views

## 1 answer to this question.

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))
```

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

## Related Questions In Others

Try the following formula- =TEXTJOIN(" ",1,FILTER(\$A\$1:\$C\$1,A2:C2<>"")&":"&FILTER(A2:C2,A2:C2<>"")) To make it ...READ MORE

## Find the serial number with criteria

Use the filter function: =FILTER(\$C\$11:\$C\$39;(\$H\$11:\$H\$39=MAXIFS(\$H\$11:\$H\$39;\$N\$11:\$N\$39;4))*(\$N\$11:\$N\$39=4)) Maybe you have to ...READ MORE

## VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

## VBA Change Cell colors based on value, and it can deal with single cell and multiple cells changes

Before looping through all of the cells ...READ MORE

## INDEX formula in Excel, Top 10, repeats previous value

Try this formula in cell W4: =IF(V3=V4,INDEX(INDIRECT("I"&MATCH(W3,I:I,0)+1&":I26"),MATCH(V4,INDIRECT("R"&MATCH(W3,I:I,0)+1&":R26"),0)),INDEX(\$I\$2:\$I\$26,MATCH(V4,\$R\$2:\$R\$26,0))) The calculation ...READ MORE

## How to use an increment an average formula by more than one row in excel?

I believe OFFSET makes it simpler, for ...READ MORE