Search code examples
excelfunctionindexingmax

find the serial number with criteria

I have an Excel database, and they are asking me to find the 8-digit pserial value for the oldest current smoker in the survey. The person is a smoker if cigst is 4. The pserial numbers are in column 3 and smokers are in column N.Age is column

this is the database, but there is more data inside the database:

enter image description here

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

Solution

  • If there are several people with the same age, it is easiest to 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 replace semicolon with comma, depending of your Excel version.

    enter image description here