I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.

EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500

Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)

you can do that with a sumproduct function - note you will get the sum of all
matches of the two criteria.

with your data in a2:d5 and desired id in a7, desired date in b7, this does
"lookup" on column C

=SUMPRODUCT((A2:A5=A7)*(B2:B5=B7)*(C2:C5))

"Nav" wrote:

I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.

EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500

Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)

Assumptions:

A1:D1 contains your headers/labels

A2:D4 contains your data

Formula:

=INDEX($D$2:$D$4,MATCH(1,($A$2:$A$4=F2)*($B$2:$B$4 =G2),0))

....where F2 contains the ID of interest, and G2 contains the date of
interest.

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER

Hope this helps!

In article ,
Nav wrote:

I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.

EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500

Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)

Thanks for the below, but do you know if this will work if the data are on
different worksheets?

"Domenic" wrote:

Assumptions:

A1:D1 contains your headers/labels

A2:D4 contains your data

Formula:

=INDEX($D$2:$D$4,MATCH(1,($A$2:$A$4=F2)*($B$2:$B$4 =G2),0))

....where F2 contains the ID of interest, and G2 contains the date of
interest.

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER

Hope this helps!

In article ,
Nav wrote:

I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.

EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500

Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)

Thanks, This works.

"duane" wrote:

you can do that with a sumproduct function - note you will get the sum of all
matches of the two criteria.

with your data in a2:d5 and desired id in a7, desired date in b7, this does
"lookup" on column C

=SUMPRODUCT((A2:A5=A7)*(B2:B5=B7)*(C2:C5))

"Nav" wrote:

I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.

EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500

Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)

You can use this kind of syntax:

=index(othersheet!$c$1:$c$100,
match(1,(a2=othersheet!$a$1:$a$100)*(b2=othersheet !$b$1:$b$100),0))
(one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

Nav wrote:

Thanks for the below, but do you know if this will work if the data are on
different worksheets?

"Domenic" wrote:

Assumptions:

A1:D1 contains your headers/labels

A2:D4 contains your data

Formula:

=INDEX($D$2:$D$4,MATCH(1,($A$2:$A$4=F2)*($B$2:$B$4 =G2),0))

....where F2 contains the ID of interest, and G2 contains the date of
interest.

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER,
not just ENTER

Hope this helps!

In article ,
Nav wrote:

I have a list of data (2 sets) , I need the data to be picked up from from 1
set if the Customer ID and the Date of purchase match together. I am aware
VLOOKUP will match and lokkup if one of the sets of data agrees, but is it
possible to pick up the 4th column if the customer ID AND the date match from
the first data set to the second data set? Please can someone help. Thanks
in advance.

EG.
Data set 1
ID Date Qty Total
1 1/12/05 5 1500
1 3/12/05 5 1500
2 2/12/05 10 2500

Data set 2
ID Date Total
1 3/12/05 (= formulae required to match both ID and date as
criteria.)


--

Dave Peterson