# Thread: Question: How to SUM a variable number of VLOOKUP calls

1. ## Question: How to SUM a variable number of VLOOKUP calls

Hello, I am trying to create a tool based in excel to be used by some data entry people in our company. I have two tables one that defines valid part names and their corresponding prices (sheet 1). And the other which is used to compose specific sale groups (sheet 2).

I would like to have the cost in sheet 2 determined by a sum of the prices of the parts in the row. I know I can use VLOOKUP to get the value of each part from sheet 1. However part of the goal is that both sheet 1 and sheet 2 will grow. So I want a formula for cost that takes into account that new parts and prices will be added to sheet 1 and that more parts can be added to sheet 2.

Any tips or help would be much appreciated.

Sheet 1 Sheet 2
 Part Name Price Valve 12 Pipe 6 Lens 20
 Name Cost Part 1 Part 2 Part 3 Sale 1 ? Valve Pipe Sale 2 ? Pipe Sale 3 ? Lens Pipe Valve Sale 4 ? Pipe Pipe

2. Formatting got messed up.

Here is an example Excel File. Examle.xlsx

3. Array-Enter (using Ctrl+Shift+Enter, not just Enter on the keyboard) the following formula in cell B2:
=SUMPRODUCT(IF(\$C2:\$E2=Prices!\$A\$2:\$A\$4,Prices!\$B\$2:\$B\$4,0))
then copy down.

4. Thanks, that worked great!