Lookup Formula help

Machala Sentance 

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim wsh As Worksheet
    Dim rng As Range
    Dim r As Long
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Range("C3:D" & Rows.Count).ClearContents
    If Not Intersect(Range("B3"), Target) Is Nothing Then
        If Range("B3").Value <> "" Then
            r = 2
            For Each wsh In ThisWorkbook.Worksheets
                If wsh.Name <> "Summary" Then
                    Set rng = wsh.Range("B:B").Find(What:=Range("B3").Value, LookAt:=xlWhole)
                    If Not rng Is Nothing Then
                        r = r + 1
                        Range("C" & r).Value = wsh.Name
                        Range("D" & r).Value = rng.Offset(0, 1).Value
                    End If
                End If
            Next wsh
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open it.

See the attached version.