Match staff name and date then import data from matched row (Example attached)

Hyflex,

Does it have to be a macro? What you're looking for can be accomplished with a formula solution. Attached is a modified version of your example workbook.
In 'AMOStaffList' I left the Manually Entered values alone and put the formula in cell CB9 copied over and down:
=INDEX(MDPData!H$2:H$20,MATCH(1,INDEX((MDPData!$F$2:$F$20=$A9)*(INT(MDPData!$D$2:$D$20)=$BW9),),0))

The results are identical.

Hyflex,

Thank you for the rep

As for the formula changes requested, give this a try:

=IFERROR(INDEX(MDPData!H$2:H$20,MATCH(1,INDEX((MDPData!$F$2:$F$20=TRIM(LEFT(SUBSTITUTE($A9&" "," ",REPT(" ",99)),99)))*(INT(MDPData!$D$2:$D$20)=$BW9),),0)),"")

Slightly simplified version:

=IFERROR(INDEX(MDPData!H$2:H$20,MATCH(1,INDEX((MDPData!$F$2:$F$20=LEFT($A20&" ",FIND(" ",$A20&" ")-1))*(INT(MDPData!$D$2:$D$20)=$BW20),),0)),"")

Hyflex,

Didn't realize it was almost 400,000 rows O.o
Give this macro a try, let me know how it goes.

Sub tgr()
    
    Dim wsAMO As Worksheet
    Dim wsMDP As Worksheet
    Dim rIndex As Long
    Dim DataIndex As Long
    Dim lDate As Long
    Dim rngFound As Range
    Dim strName As String
    Dim strFirst As String
    Dim arrData() As Variant
    
    Set wsAMO = Sheets("AMOStaffList")
    Set wsMDP = Sheets("MDPData")
    arrData = wsAMO.Range("BY1:BZ" & wsAMO.Cells(Rows.Count, "A").End(xlUp).Row).Value
    
    For rIndex = 1 To wsAMO.Cells(Rows.Count, "A").End(xlUp).Row
        If Trim(wsAMO.Cells(rIndex, "A")) <> vbNullString Then
            If Val(wsAMO.Cells(rIndex, "BW").Value) > 0 Then
                strName = Evaluate("=Left(""" & wsAMO.Cells(rIndex, "A").Value & " " & """,Find("" "",""" & wsAMO.Cells(rIndex, "A").Value & " " & """)-1)")
                lDate = Int(wsAMO.Cells(rIndex, "BW").Value2)
                Set rngFound = wsMDP.Columns("F").Find(strName, , , xlWhole)
                If Not rngFound Is Nothing Then
                    strFirst = rngFound.Address
                    Do While Not rngFound Is Nothing
                        If Int(wsMDP.Cells(rngFound.Row, "D").Value2) = lDate Then
                            arrData(rIndex, 1) = wsMDP.Cells(rngFound.Row, "H").Value
                            arrData(rIndex, 2) = wsMDP.Cells(rngFound.Row, "I").Value
                            Exit Do
                        End If
                        Set rngFound = wsMDP.Columns("F").Find(strName, rngFound, xlValues, xlWhole)
                        If rngFound.Address = strFirst Then Exit Do
                    Loop
                    strFirst = vbNullString
                    Set rngFound = Nothing
                End If
            End If
        End If
    Next rIndex
    
    Range("BY1:BZ1").Resize(UBound(arrData, 1)).Value = arrData
    
End Sub

Hyflex,

In the example where you needed to cut off the non-name stuff, it had paretheses () around the non-name stuff, e.g. Larry (N3)
Is that always the case?

Update the strName = Evaluate line to the following, and it should work for you:

strName = Evaluate("=Trim(Left(""" & wsAMO.Cells(rIndex, "A").Value & "(" & """,Find(""("",""" & wsAMO.Cells(rIndex, "A").Value & "(" & """)-1))")

The logic being used is that you always only want to cut off the (xxx) after a name (if it exists). So it finds the first "(" and removes it and everything after it. This will allow people with multiple names, like Mark Coulhen, to find their own rows instead of just Mark's row.

Adding the Replace method can get rid of apostrophes:

strName = Replace(Evaluate("=Trim(Left(""" & wsAMO.Cells(rIndex, "A").Value & "(" & """,Find(""("",""" & wsAMO.Cells(rIndex, "A").Value & "(" & """)-1))"), "'", "")

You're very welcome