Tip Printed from ExcelTip.com
Find the maximum numerical value in a list based on a criteria in Microsoft Excel


The following values appear in cells A1:A8
A1
 A 0.573
A2
 N 0.580
A3
 N 0.578
A4
 X 0.525
A5
 Y 0.581
A6
 A 0.612
A7
 Y 0.556
A8
 X 0.551


To find the maximum numerical number in the range which the first character starts with Y, perform the following steps:
  1. Select column A
  2. From the data menu choose Text to Columns
  3. At step 1 of 3 select Delimited then press Next
  4. At step 2 of 3 select Space, and press Next
  5. At step 3 of 3 press finish

The Result

column A
column B
A
0.573
N
0.580
N
0.578
X
0.525
Y
0.581
A
0.612
Y
0.556
X
0.551

Enter the following criteria in cell C1: Y

Enter the formula in cell D1: {=MAX((A1:A8=C1)*(B1:B8))}

The result = 0.581


Please note: The formula is an Array Formula, after entering the formula in the cell, press F2 and than press Ctrl+Shift+Enter