For the parts lookup on this version - maybe:
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("B8:D9")) Is Nothing Then
        If Target.Address = "$D$8" Then
            Range("D8:D11").Value = Evaluate("TOCOL(XLOOKUP(" & Target.Address & ",'Storeman''s Sheet'!A:A,'Storeman''s Sheet'!A:D,"""",0),3)")
        Else
            Range("D8:D11").Value = Evaluate("TOCOL(XLOOKUP(" & Target.Address & ",'Storeman''s Sheet'!B:B,'Storeman''s Sheet'!A:D,"""",0),3)")
        End If
    End If
    Application.EnableEvents = True
End Sub