Air code - untested as no workbook provided by OP to test.
Option Explicit
Sub Srch16()
Dim ws As Worksheet
Dim i As Long, lr As Long, lrs As Long
'determine last row in Summary Worksheet. Change name if necessary
lr = Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Summary")
For i = 1 To lr
For Each ws In Worksheets
If ws.Name <> "Summary" Then
Dim res As Variant
lrs = ws.Range("A" & Rows.Count).End(xlUp).Row
On Error Resume Next
Err.Clear
res = Application.WorksheetFunction.VLookup(.Range("A" & i), ws.Range("A1:A" & lrs), 2, 0)
If Err.Number = 0 Then
.Range("A" & i).Offset(0, 1) = res
End If
End If
Next ws
Next i
End With
End Sub
Bookmarks