IF ID Match AND No. =1 Then Paste

[ktoon]

Hi All,

I am trying to copy certain data from one Excel Sheet to another (Excel 2010, Windows 7)

Sheet 1 Data

A        B               C                D
704	Smith		
709	Heaton		
711	Williams		
712	Senior		


Sheet 2 Data
    A    B      C
[TABLE="width: 98"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD="align: right"]704[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]704[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]48[/TD]
[/TR]
[TR]
[TD="align: right"]704[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]709[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]709[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="align: right"]709[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]709[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]711[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]711[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]712[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]712[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]36[/TD]
[/TR]
[TR]
[TD="align: right"]712[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]712[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]

As you can see in Sheet 2 Column A - the number represents a Unique personal ID. But multiple entries per user exist.

What I am trying to accomplish is:

IF User ID matches, then copy the Value from Sheet 2 Column C to sheet 1 Column C IF value in Sheet 2 Column B = 1. If the value in Sheet 2 Column B = 9, then it should copy Sheet 1 Column D. All other data is ignored.

This is how it should look as an example on Sheet 1 after running:

A        B               C                D
704	Smith		48
709	Heaton	24	            12
711	Williams		            36
712	Senior	36	            12

 

[Special-K99]

in Sheet1!C1
=IFERROR(VLOOKUP(A1,IF(Sheet2!B$1:B$13=1,Sheet2!A$1:C$13),3,0),"")
in Sheet1!D1
=IFERROR(VLOOKUP(A1,IF(Sheet2!B$1:B$13=9,Sheet2!A$1:C$13),3,0),"")
Note: these are array formulas, use Ctrl-Shift-Enter

copy down the columns

 

[Special-K99]

or
in Sheet1!C1
=SUMPRODUCT((A1=Sheet2!A$1:A$13)*(Sheet2!B$1:B$13=1)*(Sheet2!C$1:C$13))
in Sheet!D1
=SUMPRODUCT((A1=Sheet2!A$1:A$13)*(Sheet2!B$1:B$13=9)*(Sheet2!C$1:C$13))
and copy down the columns

though this will place zeroes where you originally had blanks, but it does remove the need for array formulas

 

[ktoon]

The SUMPRODUCT one works perfect, thanks a lot Special!!!!