tmkid,

Welcome to the forum!
The below code has the following assumptions:
-The sheet where you're entering the CheckNames formula is the first sheet
-All other sheets have the Last Names in column A
-All other sheets have the First Names in column B
-Looking for partial matches

With those in mind, give this code a try:
Public Function CheckNames(ByVal strLastName As String, ByVal strFirstName As String) As Boolean
    
    Dim ws As Worksheet
    Dim rngFound As Range
    Dim strFirst As String
    
    For Each ws In ActiveWorkbook.Sheets
        If ws.Index > 1 Then
            Set rngFound = ws.Columns("A").Find(strLastName, ws.Cells(ws.Rows.Count, "A"), xlValues, xlPart)
            If Not rngFound Is Nothing Then
                strFirst = rngFound.Address
                Do
                    If InStr(1, rngFound.Offset(, 1).Text, strFirstName, vbTextCompare) > 0 Then
                        CheckNames = True
                        Exit Function
                    End If
                    Set rngFound = ws.Columns("A").Find(strLastName, rngFound, xlValues, xlPart)
                Loop While rngFound.Address <> strFirst
            End If
        End If
    Next ws
    
End Function


Then you could use the formula like this, where A2 contains the Last Name and B2 contains the first name:
=IF(CheckNames(A2,B2),"Name Found","")