Match value and copy it to a new cell

I'm trying to build an Excel macro that will allow me to do the following:

I have two Excel sheets.

I want the macro to match all the values in Sheet1开发者_如何学Python, col A against cell A1 in Sheet2. If it matches, copy cell Dx from Sheet1 to cell D1 in Sheet2. If it doesn't, just move on to cell A2 in Sheet2 and do the same, but copy cell Dx from Sheet1 to cell D2.

I tried the vlookup method and I failed.

=vlookup($A1,CELLREF-SHEET1,column(),false)

I'm also trying to implement this method that I've written for another project:

Dim cel As Range, celFound As Range, rg As Range, rgLookHere As Range
Dim i As Long
Dim v As Variant
Set rg = Range("A1") 'First cell to check
Set rgLookHere = rg.Offset(0, 1).EntireColumn 'Check for matches in this column
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))

On Error Resume Next
ReDim v(1 To rg.Cells.Count, 1 To 1)
For Each cel In rg.Cells
i = i + 1
If cel <> "" Then
Set celFound = Nothing
Set celFound = rgLookHere.Find(cel.Value, LookIn:=xlValues, LookAt:=xlWhole)
v(i, 1) = IIf(celFound Is Nothing, "Null", "yes")
End If
Next
rg.Offset(0, 2).Value = v
On Error GoTo 0

Do you have any advice?

VLookup seems to be the way to go. If I have data in Column A and D on Sheet1, and the data to match to in Column A in Sheet2, then I put the following VLookup in Sheet2 Column D:

=VLOOKUP(A1,Sheet1!A:D,4,FALSE)

You will see N/A in cells where there is no match. You can always use ISNA() to identify these and based upon an IF() you could output the actual match or nothing.