Find min based on criteria, not to return zero values

[projectile]

I have this array which appears to work, however I do not want it to return zero values, just anything greater than zero.

Is this possible?

{=MIN((DATASET!$A$2:$A$78956=A2)*DATASET!$Y$2:$Y$78956)}

I also wanted to use similar formula to return the SUPPLIER who is linked to this minimum value. The supplier is stored in column Z of the DATASET sheet. I have tried Googling a way to offset the above formula to return result of column Z instead of column Y, but not getting any where.. Would appreciate some help.

thanks

 

[Sinon]

To get the minimum but not 0 try

{=MIN(IF(DATASET!$A$2:$A$78956=A2,IF(DATASET!$Y$2:$Y$78956<>0,DATASET!$Y$2:$Y$78956)))}

To get the supplier, I suggest trying VLOOKUP or INDEX/MATCH depending on your data.
to get started with these try googling them. I personally prefer videos as it explains and shows you at the same time how they work.

VLOOKUP - https://exceljet.net/tips/how-to-use-vlookup
INDEX/MATCH - https://www.youtube.com/watch?v=j5NMg9ONd84

 

[projectile]

Hi Sinon, thanks for your reply. your formula worked thanks.

With regards to the second question to return another column of the min formula, I tried the follow but it returns #REF

{=INDEX(MIN((IF(DATASET!$A$2:$A$78956=A46,IF(DATASET!$Y$2:$Y$78956<>0,DATASET!$Y$2:$Y$78956)))),0,2)}

Any ideas where i'm going wrong?

 

[projectile]

Thought this may have done the trick, but returns N/A

=INDEX(DATASET!$AH$2:$AH$78956,MATCH(MIN((IF(DATASET!$A$2:$A$78956=A50,IF(DATASET!$Y$2:$Y$78956<>0,DATASET!$Y$2:$Y$78956)))),$Y$2:$Y$78956,0))

PLease advise

 

[Aladin Akyurek]

Thought this may have done the trick, but returns N/A

PLease advise

Sheet1 (processing)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][/tr]
[tr][td]

49​

[/td][td][/td][/tr]
[tr][td]

50​

[/td][td]JAD[/td][/tr]
[tr][td]

51​

[/td][td]

3​

[/td][/tr]
[tr][td]

52​

[/td][td]

2​

[/td][/tr]
[tr][td]

53​

[/td][td]supplier[/td][/tr]
[tr][td]

54​

[/td][td]SUP-A[/td][/tr]
[tr][td]

55​

[/td][td]SUP-Q[/td][/tr]
[tr][td]

56​

[/td][td][/td][/tr]
[/table]

A50 houses the condition of interest.

In A51 control+shift+enter, not just enter:

=MIN(IF(DATASET!$A$2:$A$78956=A50,IF(ISNUMBER(1/DATASET!$Y$2:$Y$78956),
    DATASET!$Y$2:$Y$78956)))

In A52 control+shift+enter:

<strike></strike>=SUM(IF(FREQUENCY(IF(DATASET!$A$2:$A$78956=A50,IF(ISNUMBER(1/DATASET!$Y$2:$Y$78956),
     IF(DATASET!$Y$2:$Y$78956=A51,MATCH(DATASET!$AH$2:$AH$78956,DATASET!$AH$2:$AH$78956,0)))),
     ROW(DATASET!$AH$2:$AH$78956)-ROW(DATASET!$AH$2)+1),1))<strike></strike>

In A54 control+shift+enter and copy down:

=IF(ROWS(A$54:A54)<=$A$52,INDEX(DATASET!$AH$2:$AH$78956,MIN(IF(DATASET!$A$2:$A$78956=A$50,
    IF(DATASET!$Y$2:$Y$78956=A$51,IF(ISNA(MATCH(DATASET!$AH$2:$AH$78956,A$53:A53,0)),
    ROW(DATASET!$AH$2:$AH$78956)-ROW(DATASET!$AH$2)+1))))),"")

This set up picks out all distinct suppliers associated with the min value, computed using the criterion value in A50.

 

[projectile]

Thanks!

 

[Aladin Akyurek]

Thanks!

You are welcome.