|One possible solution is a VBA Change Event macro.|
Assuming you are entering the Zip in A1 of Sheet 16, this code will monitor Sheet16!A1 and when it changes, loop through all of the other sheets looking in Column W for the Zip that you entered. If it finds it, it will put the city name in Sheet16!B1.
If it doesn't find the Zip, it will return Not Valid in Sheet16!B1.
(You can eliminate the need for "Not Valid" by using a Data Validation Drop Down list referring to all of your Column W's so that it will only (and always) contain just the zips from your other sheets. All you would need to do is choose the Zip that you are looking for from the Drop Down)
To use the code, create your 16th tab as the last sheet in the workbook. Right click the sheet tab for the "16th" sheet and choose View Code.
Paste this into the window that opens and enter a Zip in Sheet16!A1.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim shtNum As Integer
Dim zip As Range
'Determine if change was made to A1
If Target.Address = "$A$1" Then
Application.EnableEvents = False
'Loop through all sheets except the last one
For shtNum = 1 To Sheets.Count - 1
'Search for the Zip Code entered
Set zip = .Find(Target.Value, lookat:=xlWhole, LookIn:=xlValues)
'If found, put city name in B1 and leave loop
If Not zip Is Nothing Then
Target.Offset(0, 1) = Sheets(shtNum).Range("X" & zip.Row)
'If zip is not found, display Not Valid in B1
Target.Offset(0, 1) = "Not Valid"
Application.EnableEvents = True