Here you are, I have done it but NOT using Vlookup which is a very slow and inefficient way of coding. I never use it in VBA because using variant arrays is always faster and much more flexible.
Sub insert_ColumnAndVlookup()
'If False Then ' I put this in when testing to avoid adding more and more columns
Application.DisplayAlerts = False
ActiveSheet.Columns("c:c").Insert shift:=xlToRight, copyorigin:=xlformatfromrightorabove
ActiveSheet.Columns("c:c").Insert shift:=xlToRight, copyorigin:=xlformatfromrightorabove
Range("C1:C2").Select
Selection.Merge
Range("D1:D2").Select
Selection.Merge
Range("C1:C2").Select
ActiveCell = "Region"
Range("D1:D2").Select
ActiveCell = "Zone"
Cells(Rows.Count, 2).End(xlUp).Offset(0, 1).Select
Range(Selection, Selection.End(xlUp).Offset(1, 0)).Select
Selection.Value = "NorthEast"
Selection.End(xlUp).Select
'End If ' part of the if false above
MasterName = ActiveWorkbook.Name
'pick up data table from test workbook
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
table1 = Range(Cells(1, 1), Cells(lastrow, 1))
' initialise output array
Range(Cells(3, 4), Cells(lastrow, 4)) = ""
outarr = Range(Cells(1, 4), Cells(lastrow, 4))
'to openfile
' uncomment this line to open the workbook
'Workbooks.Open Filename:="B:\SAMPLENAME.xlsx"
' once you have done that you don't need this line
Windows("SAMPLENAME.xlsx").Activate
lastsample = Cells(Rows.Count, "A").End(xlUp).Row
table2 = Range(Cells(1, 1), Cells(lastsample, 2))
' loop through all of Test
For i = 3 To lastrow
' loop thorugh all of sample, for each row in test
For j = 2 To lastsample
If table1(i, 1) = table2(j, 1) Then
'we have found the match, so write out to the output array
outarr(i, 1) = table2(j, 2)
' stop the sample loop for this test row since we have found it
Exit For
End If
Next j
Next i
Windows(MasterName).Activate
' write out the output
Range(Cells(1, 4), Cells(lastrow, 4)) = outarr
End Sub