Multiple lookup criteria

[Cam1234]

Hi All,

I have two sheets. The first one has part numbers down column A, and Qty
accross row 1. There are 12 Qty dates accross row 1, one for each month of
the year.

The second table is 3 columns, column A is part numbers, column B is a
month, and column C is a quantity.

I have many parts with many dates, although there is only one entry for
every possible part-date combination.

What I want is for excel to look at all the data on the second sheet, find a
part number from column A in sheet 1, and return the qty that matches the
date in row1 of sheet 1.

 

[John C]

Assuming your dates written across the row in Sheet 1 are entered the same as
the dates in the second column on Sheet2, the followign formula should work.
This would be the formula for cell B2 (parts in column A, months in row 1,
therefore January of first part is in B2). Copy this formula over as far as
needed, and then down as far as needed:
=SUMPRODUCT(--(Sheet2!$A$2:$A$100=$A2),--(Sheet2!$B$2:$B$100=B$1),(Sheet2!$C$2:$C$100))

Obviously, if the range on Sheet2 is over row 100, expand as you need to.

 

[Cam1234]

Thanks John, I'll give it a try.

What does the '--' do in a formula?

 

[John C]

It's a double unary. The 2 areas that it is being used will usually evauluate
to a true or a false, the double unary makes it a number representation of
true (1) or false (0). If you search under double unary, I am sure there are
better explanations out there.