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,020 points
396 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,720 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,720 points
414 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,720 points
782 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,720 points
1,847 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,720 points
641 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,720 points
891 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,720 points
661 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,720 points
897 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,720 points
1,376 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,720 points
7,848 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,720 points
442 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