How do you perform a two-column lookup?

[Guest]

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.

 

[Dave O]

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/micr...ct+double+unary&rnum=9&hl=en#066bd4f93886f512
....explains the --, which is called a double unary operator. Very cool
stuff.

 

[Guest]

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]

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.

 

[Guest]

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)

 

[Guest]

Dave O,

I think I have this figured out. Thanks for your help!