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