Find the serial number with criteria

0 votes

They want me to locate the 8-digit Pserial number for the oldest current smoker in the poll using my Excel database. If cigst is 4, the person smokes. Column 3 has the serial numbers, while column N lists the smokers. Column for age

This is the database, but the database also contains additional information:

enter image description here

I think the functions to use are INDEX, MAX and IF, but not sure on what order to use.

Jan 10 in Others by Kithuzzz
• 28,900 points
53 views

1 answer to this question.

0 votes

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 replace semicolons with commas, depending on your Excel version.

enter image description here

answered Jan 10 by narikkadan
• 53,560 points

Related Questions In Others

0 votes
1 answer

Where can I find the details of installed files?

The details of recently and locally installed ...READ MORE

answered Mar 8, 2019 in Others by Nabarupa
243 views
+1 vote
0 answers

How to split a number with coma betweeen two colunms

Jul 3, 2019 in Others by anonymous
236 views
0 votes
1 answer

Excel feet and inches to millimeters

There are certain direct formulas to convert ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 22,960 points
668 views
0 votes
1 answer
0 votes
1 answer

Excel Vlookup function to map duplicate and get the max occurence value

Assuming there is no Excel version restriction ...READ MORE

answered 4 days ago in Others by narikkadan
• 53,560 points
30 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
215 views
0 votes
1 answer

EXCEL-Function to find how many people does the oldest female live with

You can try something like this: =ROWS(FILTER(B11:B9291, T11:T9291=MAXIFS(T11:T9291,G11:G9291,2))) It ...READ MORE

answered Jan 13 in Others by narikkadan
• 53,560 points
45 views
0 votes
1 answer

Find and adjacent cell with criteria

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 ...READ MORE

answered Jan 17 in Others by narikkadan
• 53,560 points
44 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