nested iferror index match for 10k rows

hello, i am trying to insert a nested iferror/index/match formula that basically searches for a value, if its missing checks for the value in the next list, and so on

the problem is there are about 10,000 rows and even with the below code the macro is painfully slow (longer than 20 mins). Additionally i would like to use slight variations of this formula in a few more columns, which makes it even more daunting.

Please note the number of rows will be dynamic so the formula must be inserted in every row until row is blank (which it currently does as written)

Can someone kindly review the below code and let me know I could accomplish this faster?

Thanks!!!!

Sub code1()

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


    Dim ws As Worksheet
    Dim lRow As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        .Range("B3:B" & lRow).Formula = "=IFERROR(INDEX(LEFT('list1'!C[7],2),MATCH('Sheet1'!RC[-1],'list1'!C[-1],0)),IFERROR(INDEX(LEFT('list1'!C[7],2),MATCH('Sheet1'!RC[-1],'list1'!C,0)),IFERROR(INDEX(LEFT('list1'!C[7],2),MATCH('Sheet1'!RC[-1],'list1'!C[2],0)),IFERROR(INDEX(LEFT('list1'!C[7],2),MATCH('Sheet1'!RC[-1],'list1'!C[4],0)),""No matches""))))"
        .Range("B3:B" & lRow).Value = .Range("B3:B" & lRow).Value
    End With
    
    
    
 
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
   Application.ScreenUpdating = True
    
    End Sub

try using the Range.find()

thank all of you for the suggestions, how would I implement Range.find() in my existing code?