Find Max or Min Value Based on Multiple Criteria

Let’s suppose you have an example with a list of people with names, age, and place of residence. What we want to do here is to select the youngest and the oldest woman who lives in Texas.

You can copy the following example and paste it into a worksheet.

First nameLast nameSexAgeState
EmmaSmithfemale75California
LoganJohnsonmale36California
OliviaWilliamsfemale2Texas
JamesJonesmale75California
NoahBrownmale56Texas
AvaDavisfemale68Texas
SophiaMillerfemale41Texas
MiaWilsonfemale14California
LiamMooremale3Texas
AmeliaTaylorfemale42Texas

Find max or min value based on multiple criteria

Here, is what you need to do in order to find the highest and the lowest values.

  1. Enter the following formula into the empty cell.
  1. Use the Ctrl + Shift + Enter keyboard shortcut to create an array formula.
  2. Do the same for the min value. But this time use the MIN

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.