I have two worksheets. The first is the MASTER. On the second worksheet I
want to have formula that if two cells in column A and B match then insert
information from C of the MASTER to the second worksheet. I can get one
match formula but can't get the second match.
Example: MASTER
A B C
40 502 5
50 407 2

worksheet2
A B C D
40 502 10
50 502 12

I need the formula to insert C information from the MASTER to worksheet2 D
but only if A and B match.
Hope this makes sense to someone.
What I have so far is one match formula
=INDEX(MASTER!B:B,MATCH(A4,MASTER!A:A,0))
Thanks for your help.

Try this:

=SUMPRODUCT(--(MASTER!A1:A10000=Sheet2!A1),--(MASTER!B1:B10000=Sheet2!B1),MASTER!C1:C10000)

It will not work if you use a whole column ex: A:A or B:B


"Barb" wrote:

I have two worksheets. The first is the MASTER. On the second worksheet I
want to have formula that if two cells in column A and B match then insert
information from C of the MASTER to the second worksheet. I can get one
match formula but can't get the second match.
Example: MASTER
A B C
40 502 5
50 407 2

worksheet2
A B C D
40 502 10
50 502 12

I need the formula to insert C information from the MASTER to worksheet2 D
but only if A and B match.
Hope this makes sense to someone.
What I have so far is one match formula
=INDEX(MASTER!B:B,MATCH(A4,MASTER!A:A,0))
Thanks for your help.

Note sure why I need SUMPRODUCT.
If cells in column A and B of the Sheet2 match the Master (same cells) then
I want the information from Master colum C to go into cell D of Sheet2.
(each row may be different). Let's break it down. In Master, column A lists
Operation# and column B lists Part# and column C lists time it should take to
make. On Sheet2 which is for employee work I need formula to - if Operation#
and Part# (which are entered for what employee worked on) match the Master,
then insert the time into column D.
Thanks again.

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(--(MASTER!A1:A10000=Sheet2!A1),--(MASTER!B1:B10000=Sheet2!B1),MASTER!C1:C10000)

It will not work if you use a whole column ex: A:A or B:B


"Barb" wrote:

I have two worksheets. The first is the MASTER. On the second worksheet I
want to have formula that if two cells in column A and B match then insert
information from C of the MASTER to the second worksheet. I can get one
match formula but can't get the second match.
Example: MASTER
A B C
40 502 5
50 407 2

worksheet2
A B C D
40 502 10
50 502 12

I need the formula to insert C information from the MASTER to worksheet2 D
but only if A and B match.
Hope this makes sense to someone.
What I have so far is one match formula
=INDEX(MASTER!B:B,MATCH(A4,MASTER!A:A,0))
Thanks for your help.

sorry, I spoke too soon. The formula did work, however, just for one row. I
tried to copy formula and tried fill handle to apply to other rows in the
column but I got an 0. How do I apply this formula to all rows of the
worksheet? Each row must search through all rows of column A and B in the
master worksheet to find match in the second worksheet.

"Teethless mama" wrote:

Try this:

=SUMPRODUCT(--(MASTER!A1:A10000=Sheet2!A1),--(MASTER!B1:B10000=Sheet2!B1),MASTER!C1:C10000)

It will not work if you use a whole column ex: A:A or B:B


"Barb" wrote:

I have two worksheets. The first is the MASTER. On the second worksheet I
want to have formula that if two cells in column A and B match then insert
information from C of the MASTER to the second worksheet. I can get one
match formula but can't get the second match.
Example: MASTER
A B C
40 502 5
50 407 2

worksheet2
A B C D
40 502 10
50 502 12

I need the formula to insert C information from the MASTER to worksheet2 D
but only if A and B match.
Hope this makes sense to someone.
What I have so far is one match formula
=INDEX(MASTER!B:B,MATCH(A4,MASTER!A:A,0))
Thanks for your help.