vlookup on multiple references

[CanMan12]

I need a formula that will lookup two references and return a summation from a third column. I am getting stumped trying to figure out how to write it, but if it was just written, it would look like this.
(As written in cell B3 of Sheet 1)Lookup cell A3 AND cell B2 on Sheet 2, if you find both on the same row, return the summation of column B for all matches into cell B3. If you cannot find matches to both, "null". --Then the same formula would be written for every cell across and down on this sheet.

[MSP77079]

Hi CanMan, there is something missing in your explanation. So, I hope I guessed correctly.

Please Login or Register  to view this content.

I assumed that the match for cell A3 on Sheet1 should come from column A in Sheet2 and that the match for cell B2 should come from column B in Sheet2. If that is not correct, adjust this formula accordingly.

How this works is ... Sumproduct multiplies arrays, then returns the sum from all of those multiplications. The first array returns a set of True and False. These need to be converted from True/False to numbers ... we do that by multiplying by 1 (or by -1 twice ... some people claim this is actually faster than multiplying by 1 once ... I won't swear to that). Ditto for the second condition. Multiplying True by 1 returns 1. Multiplying False by 1 returns 0.
So, if either of the first 2 matches is "False", then multiplying the 3 arrays together gives 0 for that element in the result array.

Hope that explanation makes sense. If not, write back and I will show you how to prove to yourself that this actually works.

[CanMan12]

=SUMPRODUCT(--(A3=Sheet2!$A$1:$A$38),--(B2=Sheet2!$B$1:$B$38),(Sheet2!B1:B38))

I couldn't get it to work... If I follow your logic I had it written like this:
=SUMPRODUCT(--($A3=January!$A$2:$A$17246),--(B$2=January!$C$2:$C$17246),(January!$B$2:$B$17246))

I am not sure if I need an IF or AND statement in here, as well. Something like: IF A3 = January!A2:A17246 AND B2 = January!C2:C17246 THEN SUMPRODUCT for B2:B17246, for all MATCH.

I hope this makes sense...your help is appreciated!

[MSP77079]

The formula you have written appears correct for the conditions you stated.

What is being returned? #VALUE? #NA? Zero when you are certain there is a match?

The most likely causes of a zero being returned when you are certain there is a match:
+ comparing a number with text (they appear the same to you, but are different to Excel)
+ difference in capitalization
+ extra blank spaces (invisible to you, very visible to Excel)

[CanMan12]

You are correct. I had number formats for my references, yet I was looking through text formats... Excel did not like that!

Thanks for all of your help! You've saved me days of entering data!

[MSP77079]

Thanks for the feedback.