Formula to find minimum and return value from different column

[aditya7326]

For the below table, I need to write a formula which will calculate the minimum price for all the rows with Include specified as "Yes" and return respective identifier. In this case, the result should be 5 (Column A). I have included the sample file as well.


Identifier Price Include?
1 10 Yes
2 20 Yes
3 5 No
4 23 Yes
5 5 Yes
6 27 Yes
7 35 No
8 87 Yes
9 19 Yes
10 20 Yes

 

[eFms]

Hello,

You question does not address the problem of duplicates in the price column. What if the price is duplicate? In case there are duplicate prices, you can add column and make unique prices by adding small number to it. The following formula only then works (With control+shift+enter)

=INDEX(MATCH(MIN(IF(D2:D11=D2,C2:C11)),C2:C11,0),A2:A11)

See attachment also and a small explanation.

Regards,

 

[sdsurzh]

=INDEX($A$2:$A$11,MIN(IF(C2:C11="Yes",B2:B11)))

Try this

 

[eFms]

@sdsurzh

The will stuck with duplicates if not used helper column.

 

[Chihiro]

Something like below?
=INDEX(A2:A11,MATCH(MIN(IF(C2:C11="Yes",B2:B11))&"Yes",B2:B11&C2:C11,0))

Confirmed as array (CSE).

Edit: Ah duplicates were not considered. If there's duplicates you can use... below without helper.
=INDEX(A2:A11,SMALL(IF((C2:C11="Yes")*(B2:B11=MIN(IF(C2:C11="Yes",B2:B11))),B2:B11),ROW(B2:B11)-1))

Also confirmed as array (CSE).

 

[bosco_yip]

The Column "Include" specified as "Yes" and with min price in duplicate.

Array (CSE ) formula in E2 copy down :

=IFERROR(INDEX(A$2:A$11,SMALL(IF((C$2:C$11="Yes")*(MIN(IF(C$2:C$11="Yes",B$2:B$11))=B$2:B$11),ROW(A$2:A$11)-ROW(A$2)+1),ROWS(A$1:A1))),"")

Remark : But, I found Chihiro's 2nd array formula :
=INDEX(A2:A11,SMALL(IF((C2:C11="Yes")*(B2:B11=MIN(IF(C2:C11="Yes",B2:B11))),B2:B11),ROW(B2:B11)-1))
result difference from mine ?

Regards
Bosco

 

[Chihiro]

My bad. I copied and pasted 1 cell above my final formula
=INDEX(A$2:A$11,SMALL(IF((C$2:C$11="Yes")*(B$2:B$11=MIN(IF(C$2:C$11="Yes",B$2:B$11))),ROW(B$2:B$11)-1),ROW(B2)-1))

 

[aditya7326]

Thank you all. Really helpful.

 

[Ufoo]

=INDEX($A$2:$A$11,MIN(IF(C2:C11="Yes",B2:B11)))

Try this

Thanks but your formula does not work if you change the first minimum value

 

[bines53]

Hello friends,

It is also possible that,

=AGGREGATE(15,6,$A$2:$A$11/((C$2:C$11="Yes")*(B$2:B$11=MIN($B$2:$B$11))),ROW(A1))

David

 

[bines53]

My previous formula, does not work in every scenario,

This formulas works for each scenario,

=INDEX(A2:A11,MATCH(1,FREQUENCY(AGGREGATE(15,6,(B2:B11/(C$2:C$11="Yes")),1),B2:B11*(C$2:C$11="Yes")),0))

=AGGREGATE(15,6,A2:A11/((C$2:C$11="Yes")*(B2:B11=AGGREGATE(15,6,B2:B11/(C$2:C$11="Yes"),1))),1)

David