What is the fastest way to pull data from one sheet to another using VBA

I have and excel file with 2 tabs, one is 166K rows and the other is 400K rows. Previously we were manually performing vlookups to pull data from the 400k row tab into the 166k row tab. I want to automate this some using VBA but am having issues with speed.

I tried an IF formula but that ran for over 30 minutes before I killed the process

 For i = 2 To Assign.UsedRange.Rows.Count
 For x = 2 To HR.UsedRange.Rows.Count

 If Assign.Cells(i, 1 ) = HR.Cells(x,1) Then
   Assign.Cells(i, 9) = HR.Cells(x, 3)
 
 End If

 Next x
 Next i

and now I'm trying a vlookup for VBA but that also is taking a long time.

 For x = 2 To Assign.UsedRange.Rows.Count

 On Error Resume Next
 Worksheets("Assignments").Cells(x, 9).Value = 
 Application.WorksheetFunction.VLookup(Worksheets("Assignments").Cells(x, 5).Value, 
 Worksheets("Workforce").Range("A:AX"), 5, 0)
 On Error GoTo 0
   
 Next x

any suggestions on how to speed this up? I tried using Access but the files were too big.