Nested Vlookup

[lee.dykeman]

I have Two worksheets, Sheet 1 and Sheet 2.

Sheet 1:
A B C
Invoice # Part # Invoice Date
5568 35569 12/19/05
5545 45689 12/19/05
5569 55897 12/19/05
5570 35569 12/19/05


Sheet 2:
A B C D
Invoice # Part # Invoice Date Avg. Cost
5568 35569 12/19/05 35.65
5545 45689 12/19/05 42.85
5569 55897 12/19/05 65.58
5570 35569 12/19/05 35.65

I need a formula that will find me the invoice # and part # on sheet 1
and find the matching invoice and part # on sheet 2, and then return
the average cost for the part on that invoice. There are multiple part
numbers on each invoice and many occurences of the same part number on
multiple invoices. Each part number is listed as a seperate line on
sheet 1 and 2.

So I need a formula to look at sheet 1 column a and b and find the
exact match on sheet 2.

Any ideas? Here is what I have tried does not work?
=IF((AND(VLOOKUP(B3,GDYAvg!C2:C570,1,FALSE),VLOOKUP(F3,GDYAvg!J2:J570,1,FALSE))),2,1)

Thanks,
Lee

 

[Jason]

It sounds like you could use an array formula like this:

{=SUM((Sheet2!$A$2:$A$500=Sheet1!A3)*(Sheet2!$B$2:$B$500=Sheet1B3)*Sheet2!$D$2:$D$500)}

Since this is an array formula, you don't type the curly brackets {}.
Instead of pressing enter after typing the formula, press Ctrl + Shift
+ Enter and Excel will automatically apply the brackets.

Hopefully, this is what you're looking for.

Jason