vlook up two column
 

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!

vlook up two column
 

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.
--
Thanks,
Shane Devenshire


"Hong" wrote:

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!

vlook up two column
 

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

Thank you!

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Hong" wrote:

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!

vlook up two column
 

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

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

Thank you!

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Hong" wrote:

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!

vlook up two column
 

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!



"David Biddulph" wrote:

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

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

Thank you!

"ShaneDevenshire" wrote:

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.
--
Thanks,
Shane Devenshire


"Hong" wrote:

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!