Shortening Long Nested Formula

F4TMAN

New Member
Joined
Jun 29, 2020
Messages
25
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Looking for a possibility of making this formula shorter, any ideas? Thanks

Excel Formula:
=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)
 
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...
Excel Formula:
=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.

Excel Formula:
=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.
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Glad we could help. Thanks for letting us know. :)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top