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)

 

[KRice]

If you do not expect to have multiple matches in the Division column on sheet2 (column J), then this might work for you. This approach takes each of your conditions, and if all are true, then the corresponding value in the Code column on sheet 2 (column F) is returned. I'm assuming the Code "values" are numbers, but you didn't say. If they are text, then modifications are needed. If the multiplication of the arrays results in a 0, or if a 0 is found in the Code column on the same row of interest (that matches the Division), then the result will show as 0 rather than a blank. Again, if you want a blank, then modifications would be necessary. Also...note that I eliminated the whole column references...those can slow down the worksheet.

MrExcel20210214.xlsx
	A	B	C	D	E	F
1
2						Division
3						B
4
5	Code
6	2000
F4TMAN

Cell Formulas
Range	Formula
A6	A6	=SUMPRODUCT((sheet2!$J2:$J100=$F$3)*(sheet2!$L2:$L100=0)*(sheet2!$M2:$M100>0)*(sheet2!$F2:$F100))

Sample sheet2 for testing:

MrExcel20210214.xlsx
	F	G	H	I	J	K	L	M
1	Code				Division		Actual	Budget
2	1000				A		11	10
3	2000				B		0	20
4	3000				C		33	30
5	4000				D		44	40
6
sheet2

 

[F4TMAN]

Thank you very much for your prompt response. To answer, the Divisions are unique there shouldnt be any duplicate divisions, and yes Code values contain only numbers. For some reason I'm getting the #VALUE on the formula. Any thoughts where I might be going wrong?

Thanks

 

[KRice]

Yes. You've specified a range that includes text instead of numbers...specifically this is the only one that matters...('Tab2'!$F1:$F25). This is related to my caveat earlier about text appearing in this column. SUMPRODUCT is throwing the error when it attempts to multiply the values in the first three arrays with the text value (which is the heading "Code" in cell F11 on Tab2). For a quick test, just delete "Code" from that cell to confirm that you get what is expected. Do you need the ranges to begin in row 1...or can they be changed to refer to the ranges between rows 12:16 on Tab2?

 

[KRice]

As an alternative, you could slightly modify the last array expression by wrapping it within the N function and placing a unary plus operator (+) inside to coerce an array return from the N function...as shown below. This will return numbers when there are numbers and a 0 when there is text.

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

 

[F4TMAN]

Yes. You've specified a range that includes text instead of numbers...specifically this is the only one that matters...('Tab2'!$F1:$F25). This is related to my caveat earlier about text appearing in this column. SUMPRODUCT is throwing the error when it attempts to multiply the values in the first three arrays with the text value (which is the heading "Code" in cell F11 on Tab2). For a quick test, just delete "Code" from that cell to confirm that you get what is expected. Do you need the ranges to begin in row 1...or can they be changed to refer to the ranges between rows 12:16 on Tab2?

Perfect it Worked! Must've missed that. I'll make the changes accordingly this was only a test so I'll be applying this to a much larger sheet with data. Thank you!

 

[KRice]

Great! See my second suggestion about using the N function, just in case you might encounter text in column F. Glad to help.

 

[F4TMAN]

Great! See my second suggestion about using the N function, just in case you might encounter text in column F. Glad to help.

I might be pushing it but could i had another condition, instead of having L1:L25 = 0 can we say that, if L1:L25>0 or if M1:M25>0 then return the Code value? In other words if the any of the cells unders Actual or Budget has a value greater than zero then return the code value. and if both the Actual and Budget have zero values then return a "" blank space. Thanks a million

 

[Peter_SSs]

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)),"")

 

[KRice]

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))