I have this formula in Google Sheets, I need that if the result is 70 this value is changed to 100, I'm not really sure about how to do it

=SUM((VLOOKUP(D7,Sheet1!$P$7:$Q$12,2,TRUE)*$D$3),(VLOOKUP(E7,Sheet1!$P$7:$Q$12,2,TRUE)*$E$3),(VLOOKUP(F7,Sheet1!$P$7:$Q$12,2,TRUE)*$F$3),(VLOOKUP(G7,Sheet1!$P$7:$Q$12,2,TRUE)*$G$3),(VLOOKUP(H7,Sheet1!$P$7:$Q$12,2,TRUE)*$H$3),(VLOOKUP(I7,Sheet1!$P$7:$Q$12,2,TRUE)*$I$3),(VLOOKUP(J7,Sheet1!$P$7:$Q$12,2,TRUE)*$J$3),(VLOOKUP(K7,Sheet1!$P$7:$Q$12,2,TRUE)*$K$3),(VLOOKUP(L7,Sheet1!$P$7:$Q$12,2,TRUE)*$L$3),(VLOOKUP(M7,Sheet1!$P$7:$Q$12,2,TRUE)*$M$3))

SOLUTION:

it will be like this?

=IF(SUM((VLOOKUP(D7,Sheet1!$P$7:$Q$12,2,TRUE)*$D$3),(VLOOKUP(E7,Sheet1!$P$7:$Q$12,2,TRUE)*$E$3),(VLOOKUP(F7,Sheet1!$P$7:$Q$12,2,TRUE)*$F$3),(VLOOKUP(G7,Sheet1!$P$7:$Q$12,2,TRUE)*$G$3),(VLOOKUP(H7,Sheet1!$P$7:$Q$12,2,TRUE)*$H$3),(VLOOKUP(I7,Sheet1!$P$7:$Q$12,2,TRUE)*$I$3),(VLOOKUP(J7,Sheet1!$P$7:$Q$12,2,TRUE)*$J$3),(VLOOKUP(K7,Sheet1!$P$7:$Q$12,2,TRUE)*$K$3),(VLOOKUP(L7,Sheet1!$P$7:$Q$12,2,TRUE)*$L$3),(VLOOKUP(M7,Sheet1!$P$7:$Q$12,2,TRUE)*$M$3))) => 70, 100, =SUM((VLOOKUP(D7,Sheet1!$P$7:$Q$12,2,TRUE)*$D$3),(VLOOKUP(E7,Sheet1!$P$7:$Q$12,2,TRUE)*$E$3),(VLOOKUP(F7,Sheet1!$P$7:$Q$12,2,TRUE)*$F$3),(VLOOKUP(G7,Sheet1!$P$7:$Q$12,2,TRUE)*$G$3),(VLOOKUP(H7,Sheet1!$P$7:$Q$12,2,TRUE)*$H$3),(VLOOKUP(I7,Sheet1!$P$7:$Q$12,2,TRUE)*$I$3),(VLOOKUP(J7,Sheet1!$P$7:$Q$12,2,TRUE)*$J$3),(VLOOKUP(K7,Sheet1!$P$7:$Q$12,2,TRUE)*$K$3),(VLOOKUP(L7,Sheet1!$P$7:$Q$12,2,TRUE)*$L$3),(VLOOKUP(M7,Sheet1!$P$7:$Q$12,2,TRUE)*$M$3))

CodePudding user response：

You could wrap the entire thing in an If statement, where 'formula' stands for the entirety of the formula you posted.

=If(formula = 70, 100, formula)

CodePudding user response：

try:

=LAMBDA(x, IF(x=70, 100, x))(SUMPRODUCT(VLOOKUP(D7:M7, Sheet1!P7:Q12, 2)*D3:M3))