Index Match and return only positive values

[Thomas Kuriakose]

Respected Sirs,

We have a worksheet with two tabs. We need to lookup up data from one tab (Data) which has positive values and negative values and match with the reference in the other tab (Match) and extract only positive values. The negative values should be zero.

Kindly find attached the workbook for your reference.

I input the below formula, kindly let me know whether we can have a better way to get the desired result.
=IFERROR(IF(INDEX(Data!A2:A17,MATCH(Match!C2,Data!B2:B17,0))<0,0,INDEX(Data!A2:A17,MATCH(Match!C2,Data!B2:B17,0))),0)

Thank you very much,

with regards,
thomas

 

[bines53]

Hi ,

You mean, the formula is correct, you want to improve it?

=IFERROR(AGGREGATE(15,6,(Data!$A$2:$A$17)/((Data!$B$2:$B$17=Match!C2)*(Data!$A$2:$A$17>=0)),1),0)

David

 

[bosco_yip]

Few ways can do that :

1] =IF(INDEX(Data!A$2:A$17,MATCH(C2,Data!B$2:B$17,0))<0,0,INDEX(Data!A$2:A$17,MATCH(C2,Data!B$2:B$17,0)))

2] =TEXT(INDEX(Data!A$2:A$17,MATCH(C2,Data!B$2:B$17,0)),"0.00;\0;0")

3] =SUMIFS(Data!A:A,Data!B:B,C2,Data!A:A,">0")

Remark :

Formula [2] results are text values.

and, I choose formula [1], [1] maybe faster than [3].

Regards
Bosco

 

[Thomas Kuriakose]

Respected Sirs,

Thank you very much for the variations provided.

Much appreciated.

Thanks once again.

with regards,
thomas

 

[Deepak]

One more..

=Max(0,INDEX(Data!A$2:A$17,MATCH(C2,Data!B$2:B$17,0)))

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for this variable.

This is amazing.

with regards,
thomas