find the serial number with criteria-CodePudding

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:

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

CodePudding user response：

The following should work, assuming the input data are in the range of 11-100 rows. You can adapt it to your actual range. Considering also no excel version constraint based on tags listed in the question .

=LET(f, FILTER(HSTACK(C11:C100, H11:H100), N11:N100=4), fa, INDEX(f,,1), 
  fb, INDEX(f,,2), TEXTJOIN(",",,FILTER(fa, fb=MAX(fb))))

You filter first a subset of pserial and age columns based on cigst column values equal to 4, then just find the maximum. Preventing more than one pserial value correspond to the maximum age, we use TEXTJOIN to concatenate the result. If it is not expected for your real case, then you can remove this last step.

CodePudding user response：

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.