multiple vlookup, sum(vlookup), sum vlookup results in a single formula
Consider the problem as below:
The table gives the sales figures for each of the sales person across five products
Say we need to find the total sales for identified salesperson across 3 products – Prod1, Prod2 and Prod5
One way to do this is to carry out vlookup thrice and sum them up. Here is the same solution but with a single vlookup entered as an array formula.
The formula in cell B10 is {=SUM(VLOOKUP(A10,A3:F7,{2,3,6},FALSE))} entered as an array formula with ctrl+shift+enter
The formula carries out vlookup for each of the col_index_num in the array viz. 2,3 and 6 and then sums the result.
Let me know if it ever speeds up your task 🙂
[…] In one of my earlier posts, I had illustrated passing an array for col_index_num in vlookup. Refer Extract and sum vlookup results. […]