# combining formula in one

#### sachin483

##### Board Regular
i have the formula which checks 5 criteria and 2 formula check the other 2 column which i put by (ctrl+shift+del) can we combine both the formula

1. formula

=MIN(MAX(G3*INDEX(\$S\$3:\$S\$138,L3)%,E3+INDEX(\$X\$3:\$X\$138,L3)),MAX(G3*INDEX(\$T\$3:\$T\$138,L3)%,E3+INDEX(\$Y\$3:\$Y\$138,L3)),MAX(G3*INDEX(\$U\$3:\$U\$138,L3)%,E3+INDEX(\$Z\$3:\$Z\$138,L3)),MAX(G3*INDEX(\$V\$3:\$V\$138,L3)%,E3+INDEX(\$AA\$3:\$AA\$138,L3)),MAX(G3*INDEX(\$W\$3:\$W\$138,L3)%,E3+INDEX(\$AB\$3:\$AB\$138,L3)))

2.formula- array ctrl+shift+del

=MATCH(1,(\$Q\$3:\$Q\$138=A3)*(\$R\$3:\$R\$138=B3),0)

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Presumably, your second formula is in L3. You can just put that formula in your first formula wherever you see L3, and confirm it with Control+Shift+Enter. You can switch the formula to a non-array formula by using something like this as your second formula:

=LOOKUP(2,1/((\$Q\$3:\$Q\$138=A3)*(\$R\$3:\$R\$138=B3)),ROW(\$Q\$3:\$Q\$138)-ROW(\$Q\$3)+1)

However, I don't recommend this course of action! Either version of the second formula is a pretty intensive calculation, you don't want to force the first formula to calculate it 10 times. That's the whole point of helper cells, to calculate some things to make other formulas simpler.

Incidentally, you can shorten your first formula by changing it to an array formula:

=MIN(IF(G3*INDEX(\$S\$3:\$W\$138,L3,0)%>E3+INDEX(\$X\$3:\$AB\$138,L3,0),G3*INDEX(\$S\$3:\$W\$138,L3,0)%,E3+INDEX(\$X\$3:\$AB\$138,L3,0)))

with Control+Shift+Enter. This uses some nice properties of INDEX, along with the design of your sheet.

Thanks @Eric W the formula is working fine (second formula is in column L) but it is possible to combine this 2 formula's in one

Presumably, your second formula is in L3. You can just put that formula in your first formula wherever you see L3, and confirm it with Control+Shift+Enter. You can switch the formula to a non-array formula by using something like this as your second formula:

=LOOKUP(2,1/((\$Q\$3:\$Q\$138=A3)*(\$R\$3:\$R\$138=B3)),ROW(\$Q\$3:\$Q\$138)-ROW(\$Q\$3)+1)

However, I don't recommend this course of action! Either version of the second formula is a pretty intensive calculation, you don't want to force the first formula to calculate it 10 times. That's the whole point of helper cells, to calculate some things to make other formulas simpler.

Incidentally, you can shorten your first formula by changing it to an array formula:

=MIN(IF(G3*INDEX(\$S\$3:\$W\$138,L3,0)%>E3+INDEX(\$X\$3:\$AB\$138,L3,0),G3*INDEX(\$S\$3:\$W\$138,L3,0)%,E3+INDEX(\$X\$3:\$AB\$138,L3,0)))

with Control+Shift+Enter. This uses some nice properties of INDEX, along with the design of your sheet.
Or am I missing your point?

No I was just assuming that helper formula can be added in main formula it can be done

Replies
7
Views
99
Replies
12
Views
366
Replies
1
Views
106
Replies
1
Views
390
Replies
11
Views
586

1,196,409
Messages
6,015,104
Members
441,870
Latest member
kojack

### 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.

### Which adblocker are you using?

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

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