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 name Last name Sex Age State
Emma Smith female 75 California
Logan Johnson male 36 California
Olivia Williams female 2 Texas
James Jones male 75 California
Noah Brown male 56 Texas
Ava Davis female 68 Texas
Sophia Miller female 41 Texas
Mia Wilson female 14 California
Liam Moore male 3 Texas
Amelia Taylor female 42 Texas

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 min value. But this time use the MIN