# Vlookup Formula mixed with Sumproduct Formula Problem

1. ## Vlookup Formula mixed with Sumproduct Formula Problem

I have a set of four columns of data. I'd like to create a formula that can look at an input from data in column 1 to pick data point(s) from the corresponding row(s) in column 2 and multiply it by data point(s) from the corresponding row(s) in column 3.

I think I could solve this by creating a vlookup for column 2 and multiplying by the result of another vlookup for the column 3, but their might be several rows that match the input in column 1, and I would like the sum of all those matches.

Kind of hard to explain, so I've included an attachment with samples of what I would like the input to be (looking at data in column 1) and what the output to be (multiplying matches in column 2 and column3).

Any help or advice is greatly appreciated!

2. ## Re: Vlookup Formula mixed with Sumproduct Formula Problem

in B16

=SUMPRODUCT((\$B\$2:\$B\$10)*(\$A\$2:\$A\$10=B\$15)*(\$C\$2:\$C\$10))

in B17

=SUMPRODUCT((\$B\$2:\$B\$10)*(\$A\$2:\$A\$10=B\$15)*(\$D\$2:\$D\$10))d

OR

B16

=SUMPRODUCT((\$B\$2:\$B\$10)*(\$A\$2:\$A\$10=B\$15)*(INDEX(\$C\$2:\$D\$10,,ROWS(\$1:1))))

copy down to B17

3. ## Re: Vlookup Formula mixed with Sumproduct Formula Problem

Cell B16 formula , Drag down

HTML Code:
``=SUMPRODUCT((\$A\$2:\$A\$10=B\$15)*(\$B\$2:\$B\$10)*OFFSET(\$C\$2:\$C\$10,,ROW(A1)-1))``

There are currently 1 users browsing this thread. (0 members and 1 guests)