Hi,

Am Thu, 1 Feb 2018 09:03:44 -0600 schrieb leonard hofstatder:

I think Index Match is what I want but not getting results I want: (and
that's why I'm here asking, you all are the best :-)

Sheet 1 - table 1 - criteria E5:G176

Col E Col G
MWFS TSBGE
MWFS TSBGK
MWF PURNC
MF NPUOT
WS SMPHP
M SCCS
M WESTVS
W MUGMC
F STAHM

Sheet 2 - return results E5:E176

I need to match all cells that contain M(onday) in column E on sheet1
and return the customer code that is in column G sheet1

Sheet 3 will be for W(ednesday)
Sheet 4 will be for F(riday)
Sheet 5 will be for S(aturday)

try it with VBA:

Sub Test()
Dim myRng As Range, c As Range
Dim varCheck As Variant
Dim FirstAddress As String
Dim i As Integer, n As Integer

varCheck = Array("M", "W", "F", "S")
Set myRng = Sheets("Sheet1").Range("E5:E176")

For i = LBound(varCheck) To UBound(varCheck)
n = 5
Set c = myRng.Find(varCheck(i), LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
Sheets("Sheet" & i + 2).Cells(n, "E") = _
c.Offset(, 2)
n = n + 1
Set c = myRng.FindNext(c)
Loop While c.Address < FirstAddress
End If
Next
End Sub


Regards
Claus B.
--
Windows10
Office 2016