Find and adjacent cell with criteria

0 votes

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)
Jan 17 in Others by Kithuzzz
• 28,900 points
44 views

1 answer to this question.

0 votes

Using FILTER() & MAXIFS()

enter image description here


• 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()

enter image description here


• 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()

enter image description here


• 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)))
answered Jan 17 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Textjoin with adjacent headers and rows with a twist

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

answered Jan 10 in Others by narikkadan
• 53,520 points
35 views
0 votes
1 answer

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

answered Jan 10 in Others by narikkadan
• 53,520 points
53 views
0 votes
1 answer

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

answered Jan 20 in Others by narikkadan
• 53,520 points
52 views
0 votes
1 answer
0 votes
1 answer

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

answered Oct 8, 2022 in Others by narikkadan
• 53,520 points
163 views
0 votes
1 answer

How Can I Round Prices to the nearest 0.95 with an Excel Formula?

Try this: =IF(OR(A3-FLOOR(A3,1)>0.95,A3=CEILING(A3,1)),CEILING ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 53,520 points
129 views
0 votes
1 answer

Excel Formula with Nested IF/LEFT/AND Functions

Use this: =IF(SUMPRODUCT(--(LEFT(G3,1)={"1","2","3"}))>0,"998", ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 53,520 points
141 views
0 votes
1 answer

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

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

answered Nov 24, 2022 in Others by narikkadan
• 53,520 points
147 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 53,520 points
1,510 views
0 votes
1 answer

Excel Conditional Formating to find numbers a cell with text

Try this: =OR(ISNUMBER(-MID(SUBSTITUTE(A1," ","~")&"~",seq,4))) where seq is a defined name that ...READ MORE

answered Jan 10 in Others by narikkadan
• 53,520 points
28 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP