Match Columns and if match return specific value

Question

How can I create a VBA code that will match 2 reference numbers in different columns and return data in third column.

The reference numbers are located in column A (Sheet1) and column A (Sheet2) If a match is found, then the Dept. in Sheet 1, Column C, will be copied into an empty column in Sheet 2 Column B.

The code that I have written so far matches data in both columns but only for specific words.

Private Sub CommandButton1_Click()
a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To a

If Worksheets("Sheet1").Cells(i, 3).Value = "North" Then

Worksheets("Sheet1").Rows(i).Copy
Worksheets("sheet2").Activate
b = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("sheet2").Cells(b + 1, 1).Select
ActiveSheet.Paste
Worksheets("Sheet1").Activate

End If
Next
 Application.CutCopyMode = False
 ThisWorkbook.Worksheets("Sheet1").Cells(1, 1).Select

 End Sub

Answer 1

You don't need VBA for this: Juse use a VLOOKUP

---

See formula used in Column H