Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do you perform a two-column lookup?
I am trying to do a lookup using 2 columns. Example would be
SKU Warehouse Qty 13 CA 15 14 CA 30 15 CA 18 13 HN 10 14 HN 5 I want to be able to pull the qty for any given SKU Warehouse combination. Any help would be great. |
#2
|
|||
|
|||
How do you perform a two-column lookup?
Assuming your headers are in row 1 and the data in A2:C6, use
SUMPRODUCT with double unary operators, comme ca: =SUMPRODUCT(--($A$2:$A$6=13),--($B$2:$B$6="CA"),$C$2:$C$6) This link http://groups.google.com/group/micro...6bd4f93886f512 ....explains the --, which is called a double unary operator. Very cool stuff. |
#3
|
|||
|
|||
How do you perform a two-column lookup?
Hello Dave O,
Thank you for your post but not exactly what I was looking for. I have two spreadsheets. One has the information, SKU, Warehouse and Qty. I need to copy and paste the qty information into another spreadsheet but in a different order so it cant be straight copy. So I need the lookup to look at both the SKU and Warehouse combination to populate the qty. I dont want the formula to have any text data so that I can copy the formula to each cell. Hope that makes sense. Kymm "Dave O" wrote: Assuming your headers are in row 1 and the data in A2:C6, use SUMPRODUCT with double unary operators, comme ca: =SUMPRODUCT(--($A$2:$A$6=13),--($B$2:$B$6="CA"),$C$2:$C$6) This link http://groups.google.com/group/micro...6bd4f93886f512 ....explains the --, which is called a double unary operator. Very cool stuff. |
#4
|
|||
|
|||
How do you perform a two-column lookup?
I think I get you: you need to lookup to populate the QTY information
to the second spreadsheet. You can use the formula I posted in the QTY column on the second sheet- the ranges, currently A2:A6, B2:B6, C2:C6, should refer to the first sheet and the 13 and "CA" can refer to cells on the second sheet. |
#5
|
|||
|
|||
How do you perform a two-column lookup?
I am sorry I dont understand.
How would I write the formula to find the following: Spreasheet 1 Spreadsheet 2 SKU Warehouse Qty SKU Warehouse Qty 13 CA 15 13 CA (formula to copyt to 14 CA 30 14 HN to each cell, ie 15 CA 18 13 HN match column a & b 13 HN 10 14 CA of spreadsheet 1 to 14 HN 5 15 CA column a & b of spreadsheet 2 to find qty) "Dave O" wrote: I think I get you: you need to lookup to populate the QTY information to the second spreadsheet. You can use the formula I posted in the QTY column on the second sheet- the ranges, currently A2:A6, B2:B6, C2:C6, should refer to the first sheet and the 13 and "CA" can refer to cells on the second sheet. |
#6
|
|||
|
|||
How do you perform a two-column lookup?
Dave O,
I think I have this figured out. Thanks for your help! "Kymm" wrote: I am sorry I dont understand. How would I write the formula to find the following: Spreasheet 1 Spreadsheet 2 SKU Warehouse Qty SKU Warehouse Qty 13 CA 15 13 CA (formula to copyt to 14 CA 30 14 HN to each cell, ie 15 CA 18 13 HN match column a & b 13 HN 10 14 CA of spreadsheet 1 to 14 HN 5 15 CA column a & b of spreadsheet 2 to find qty) "Dave O" wrote: I think I get you: you need to lookup to populate the QTY information to the second spreadsheet. You can use the formula I posted in the QTY column on the second sheet- the ranges, currently A2:A6, B2:B6, C2:C6, should refer to the first sheet and the 13 and "CA" can refer to cells on the second sheet. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Column Data lookup | Excel Discussion (Misc queries) | |||
Column Lookup | Excel Discussion (Misc queries) | |||
Lookup last entry in column | Excel Discussion (Misc queries) | |||
Lookup then Match and insert value from next column | Excel Worksheet Functions | |||
How to lookup data in a row and column | Excel Discussion (Misc queries) |