Shortening Long Nested Formula

[F4TMAN]

Looking for a possibility of making this formula shorter, any ideas? Thanks

=IF(AND(INDEX('Tab2'!$F:$M,MATCH($F$3,'Tab2'!$J:$J,0),7)=0,INDEX('Tab2'!$F:$M,MATCH($F$3,'Tab2'!$J:$J,0),8)>0),INDEX('Tab2'!$F:$M,MATCH($F$3,'Tab2'!$J:$J,0),1),"")

This formula is producing this
The active cell is A6. I'm looking to include a formula in A6 that will give me this result.
Lookup Division (F3), and in sheet2 under (column J) IF you find a match , look in the same row under Actual (Column L). IF under Actual shows Zero and then look under Budget , IF budget shows a
number greater than zero, then return the value listed under Code (column F)

 

[F4TMAN]

Sure...as Peter's solution in 365 illustrates, the OR condition implies which conditions are added together. In Excel 2019, following the earlier approach I described and accounting for potential text in the Code column, the formula would look like this...

=SUMPRODUCT(('Tab2'!$J1:$J25=$F$3)*(('Tab2'!$L1:$L25>0)+('Tab2'!$M1:$M25>0))*N(+'Tab2'!$F1:$F25))

If you are using your Excel 365 and there will be at most one row that matches the conditions then a possibility might be as follows.
I'm using the ranges more aligned to what you showed in post #3 but you can adjust those as required.

=FILTER('Tab2'!$F$12:$F$25,('Tab2'!$J$12:$J$25=$F$3)*(('Tab2'!$L$12:$L$25>0)+('Tab2'!$M$12:$M$25>0)),"")

Very Grateful for both responses. They both solve my problem flawlessly so thank you both.

 

[Peter_SSs]

Glad we could help. Thanks for letting us know.