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, 2023 in Others by Kithuzzz
• 38,010 points
507 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, 2023 by narikkadan
• 63,420 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
388 views
+1 vote
0 answers

How to split a number with coma betweeen two colunms

Jul 3, 2019 in Others by anonymous
468 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
• 23,260 points
1,346 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 Feb 4, 2023 in Others by narikkadan
• 63,420 points
251 views
0 votes
1 answer

Type the max number title in Excel

If you have duplicates and want to ...READ MORE

answered Apr 2, 2023 in Others by Kithuzzz
• 38,010 points
293 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, 2023 in Others by narikkadan
• 63,420 points
418 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, 2023 in Others by narikkadan
• 63,420 points
243 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