match two cells from one worksheet to another

G

Guest

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.
 
G

Guest

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
 
G

Guest

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.
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top