Lookup Data From Multiple Sheets

[bakerman]

Or VBA solution.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next
    If Target.Address = "$D$5" Then
        For j = 1 To 3
            sn = Sheets(j).Columns(1).Find(Target.Value, , xlValues, xlWhole).Resize(, 2)
            If Err = 0 Then
                y = y + 1: Range("E5") = sn(1, 2)
                Exit For
            End If
            Err.Clear
        Next
        If y = 0 Then Range("E5").ClearContents: MsgBox "Employee name not found", vbInformation, "Sorry"
    End If
   
End Sub