Still working through it, but at first glance the data you are searching isn't what you thought you were seeing. Take your A76's. The value in the cell is "A76" and not "A76 "
. The ecll length is 3 characters long, so you will never get any results looking for what you are looking for. The ZIP cells are the same. The cells with A13 have 27 spaces in them, and other cells are in a similar state. There is no consistency between them, so you will never be sure that the data is good.
I would suggest you use something like Dave McRitchies TrimAll macro to clean your data as soon as you have imported it. It takes seconds and will save you so much heartache. If you want you can even tie it to a macro and a button, so that all you have to do is hit the button. This will get rid of all spaces etc in the data selected
Macro is as follows:-
Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
Cell.Value = Application.Trim(Cell.Value)
Next Cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Other point to note is that once you have cleaned your data this way, you can then actually use the data in your table as the criteria instead of having to build it into the formula. I'll fix your spreadsheet and send it back as per the above.
Regards
Ken...................
