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, 2023 in Others by Kithuzzz
• 38,010 points
242 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, 2023 by narikkadan
• 63,420 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, 2023 in Others by narikkadan
• 63,420 points
277 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, 2023 in Others by narikkadan
• 63,420 points
506 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, 2023 in Others by narikkadan
• 63,420 points
1,352 views
0 votes
1 answer

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

answered Jan 21, 2023 in Others by narikkadan
• 63,420 points
417 views
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
• 63,420 points
605 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
• 63,420 points
451 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
• 63,420 points
602 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
• 63,420 points
947 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
• 63,420 points
6,642 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, 2023 in Others by narikkadan
• 63,420 points
293 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