Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple lookup criteria
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple lookup criteria
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. -- ** John C ** "Cam1234" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple lookup criteria
Thanks John, I'll give it a try.
What does the '--' do in a formula? "John C" wrote: 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. -- ** John C ** "Cam1234" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple lookup criteria
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. -- ** John C ** "Cam1234" wrote: Thanks John, I'll give it a try. What does the '--' do in a formula? "John C" wrote: 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. -- ** John C ** "Cam1234" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup by multiple criteria? | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria, sorry if 2 pos | Excel Worksheet Functions | |||
Lookup using multiple sheets and multiple criteria | Excel Discussion (Misc queries) | |||
Lookup on multiple criteria and max | Excel Worksheet Functions | |||
Lookup with Multiple Criteria | Excel Discussion (Misc queries) |