How to sum the value of 2 rows with vlookup by only using 1 formula?-CodePudding

Here is what I did

=VLOOKUP(M3,P2:Q23,2,FALSE) VLOOKUP(N3,P2:Q23,2,FALSE)

I want to sum the values with just one formula and not repeat it

Im using Excel Online

I tried =XLOOKUP(M2:N2,P3:P23,Q3:Q23) but I get a value error,does anyone know how to do this ?

CodePudding user response：

Perhaps you can try in Excel Online:

• Formula used in cell C3

=SUM(SCAN(0,M3:N3,LAMBDA(x,y,VLOOKUP(y,P3:Q12,2,0))))

Works for me in Google Sheet as well

• Formula used in cell C3

=SUM(SCAN(0,M3:N3,LAMBDA(x,y,VLOOKUP(y,P3:Q12,2,0))))

CodePudding user response：

This works if, and only if, you are certain the M2 and N2 will not be the same value:

=SUMPRODUCT( ( (P2:P23=M2) (P2:P23=N2) ) * Q2:Q23 )

If you want N2 = M2 to be valid and result in the number added twice, then:

=SUMPRODUCT( (P2:P23=M2) * Q2:Q23 ) SUMPRODUCT( (P2:P23=N2) * Q2:Q23 )

But then you are back to repeating the formula.

I used M2 and N2 for the lookup values; your post uses row references 2 and 3 sometimes interchangeably.