Newbie - Search using dual criteria from one worksheet match on another worksheet return

Sample Formula.xlsx - I have added a sample spreadsheet of what I am doing - I hope this helps make it clearer - thanks a mill.


Hi Folks im new to excel and trying to figure out the best formula for the following;

worksheet 1

Column A = Numbers
10
32
68

Column B = Names
Jim
Jack
John

Column C = Formula -Currently using this with difficulty - =Sumproduct(--('Worksheet 2'!$A:$A=$A2),--('Worksheet 2'!$B:$B=$B2), 'Worksheet 2'!$C:$C)

Worksheet 2 - A Pivot Table

Column A = Numbers
10
12
68

Column B = Names
Jim
George
John

Column C = Values
$100
$150
$10

Ok so to me the above formula in work sheet 1 column c will take 10 from a1, find a match in worksheet 2 column a, then take jim from column b worksheet 1, then find a match in worksheet 2 column b and when both worksheet 2 column b match with worksheet 1 columns a and b the value from the corresponding cell in worksheet 2 will be returned to cell c work sheet 1. The results for this example if used over 3 cells would be, work sheet 1, c1=$100, c2=0 & c3=$10.

I am new to this and have no idea how my formula works as I borrowed it from other examples but what I would like here is the most efficient way of carrying out the same task as I am suffering a lot with the "insufficient resource" errors.

Any help greatly appreciated

Thanks folks.

Reduce your range from entire columns to only the length of rows needed for the calculation.

=Sumproduct(--('Worksheet 2'!$A$1:$A$100=$A2),--('Worksheet 2'!$B$1:$B$100=$B2), 'Worksheet 2'!$C$1:$C$100)



An alternate function you can use is SUMIFS:

=SUMIFS('Worksheet 2'!$C$1:$C$100, 'Worksheet 2'!$A$1:$A$100, $A2, 'Worksheet 2'!$B$1:$B$100, $B2)

Try switching to SUMIFS for whole column references, it's probably a wee bit more efficient.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.