Finding Smallest, 2nd Smallest Values

[Paul Corrado]

I would like to find a formula to find the smallest value in one column of a
data table based on the information in two other columns. I would guess
it would be a some type of SMALLIF function.

Example

Name Rank Crit A Crit B
A 1 AA YES
B 2 AA YES
C 3 AB YES
D 4 AA NO
E 5 AB NO

The formula would find the smallest value in "Rank" where Crit A = AA and
Crit B = YES In this case that would be Name = A. It would also be able to
find the Name = B is the second smallest value in Rank based on the same
criteria.

Any help would be greatly appreciated.

TIA

PC

 

[Frank Kabel]

Hi Paul
try the following array formula (entered with CTRL+SHIFT+ENTER)
1. The smallest value
=MIN(IF(($C$1:$C$100="AA")*($D$1:$D$100),$B$1:$B$100))

2. The second smallest number
=SMALL(IF(($C$1:$C$100="AA")*($D$1:$D$100),$B$1:$B$100),2)

 

[Paul Corrado]

Thanks. I tried the array using Small If a few times but just couldn't get
the syntax down.

PC