vlook up two column

[Hong]

Hello!

I have a set of data, Part# is 1001, 1002,....
Destionation is different
A(Part#) B(Destination) Date1 Date2 Date3...............
1001 Canada 100 105 110.....
1001 USA 50 60 70

Because the same part# have different destination, and the the same
destination has different part#. (This is a big excel sheet, and I get it
everyday with different Qty in different order)

I have another excel sheet to get the number in the same format as above,
but I am trying to use vlook up to look up two condition, for example, look
up part#: A and also look up Destination: Canada , so I can get the QTY for
date1 and so on.
I don't need to lookup the date, because the date are always in sequence.

Please help me on the formula!
Thank you!

 

[ShaneDevenshire]

Hi,

You can use something like this

=SUMPRODUCT(--(1001=$A$1:$A$1100),--("Canada"=$B$1:$B$1100),C1:C1100)

you can change the 1001 and Canada to cell references if you want. You
could modify it to handle a variable date.


If this helps, please click the Yes button.

 

[Hong]

Thank you so much, it works great!
But would you explain to me why need to use "--",
it doesn't work without "--"

Thank you!

 

[David Biddulph]

http://www.mcgimpsey.com/excel/formulae/doubleneg.html
http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Hong]

Thank you so much.

Is that possible I use VB to get the data?
For example, create a command button with programming to get the data,
rather than set all the cells with formula?

I am trying to learn the VB in excel.

Thank you!