I have the below table information and I need to lookup a value and then use a formula:

Columns A, B and C is my lookup table. Column E is the value I want to lookup and column F is the result of the formula.

F2 has the correct formula and the result but it's done by specifying the cells rather than using a vlookup. There are a lot of values I need to lookup so specifying the cells is not practical. Sometimes the value i'm looking for is in 3 rows, sometimes it's 2 etc

I tried the below formula and the result is shown in F3 but it's not correct. Anyone knows how I can achieve the same result in F2 by using a lookup formula?

Formula used (My failed attempt):

=SUMPRODUCT(VLOOKUP(E3,$A$2:$C$10,{2},FALSE))

Formula used in F2 (correct result but I specified the cells here)

=SUM(B2:B4*C2:C4) / SUM(B2:B4)

Any help appreciated. Thanks

CodePudding user response：

Use SUMPRODUCT and SUMIFS:

=SUMPRODUCT($C$2:$C$10,$B$2:$B$10,--($A$2:$A$10=E2))/SUMIFS($B$2:$B$10,$A$2:$A$10,E2)