Multi partial match conditions across two sheets

I have an index match that works; however, i cannot seem to figure out how to translate it to VBA code. Essentially, I have criteria from 4 columns that I am trying to match to 4 columns on a second spreadsheet. The second spreadsheet is a report that has character limits so most of the criteria is not an exact match.

Here's my index - match array that works

=IF(ISERROR(INDEX('2019'!C:C,MATCH("*"&Q5&"*"&"*"&C5&"*"&"*"&D5&"*"&"*"&M5&"*",'2019'!O:O&'2019'!N:N&'2019'!M:M&'2019'!P:P,0),1)),"No Match","Match")

Essentially I am looking to see if there is a 'partial' match on all 4 criteria; then place "Match" in column "S" of that row otherwise "No Match" in that column

Q5, C5, D5 and M5 are in Sheet 1; Sheet 2 = 2019, Columns O, N, M, P (respectively)

I am looking to see if there is:
A partial match of the Cell Q5 in sheet 2019 column O:O
AND
partial match of Cell C5 in sheet 2019 column N:N
AND
partial match of the Cell D5 in sheet 2019 column M:M
AND
partial match of the Cell M5 in sheet 2019 column P:P

I will check each row in sheet 1 to see if there is a match and copy that match to a separate page or just put "Match" in column "S" of Sheet 1....

Been trying to figure this out for too long .... Can anyone help?

Possibly...

Please Login or Register  to view this content.

Glad I helped!

Actually ... it didn't work Can you take another look? I've attached a workbook with just the columns populated.

When I changed the return criteria if matched to return the Journal Line (Column C of PSoft) rather than "Match" it did not return the correct value.

The rows with blue highlight should return "No Match" while the others should match

I appreciate the 2nd look.

Question: Do all the matches need to be in the same row on the 2nd worksheet?

Yes. If match found for Q on line 10, then the other conditions must be met on that same line to be considered a match and the value of column C is what would be populated in the criteria ws (ConcurExpense).
Also, There should not be more than one journal ID for the match due to the $ (column N). The journal ID (Column C - PSoft) is the same when the $ are the same. Also, maybe this will help; matches will only be found where the source (Psoft - Column F) is "CON"

Essentially, I am trying to see if all the ConcurExpense transactions have been posted in Peoplesoft (PSoft) and pulling back the journal ID to use in the next sequence ... which is to see if it has been has been billed to a third party (different worksheet).

Thank you again for your help with this.

Tried it and made a few changes. It seems to work. Thanks!

Sub ConcurExpensePSMATCH()
Dim wC As Worksheet, wS As Worksheet
Dim rC As Range, rS As Range
Dim vC As Variant, vS As Variant
Dim i As Long, j As Long

Set wC = Worksheets("ConcurExpense")
Set rC = wC.Cells(1, 1).CurrentRegion
Set rC = rC.Offset(4).Resize(rC.Rows.Count + 4)
vC = rC

Set wS = Worksheets("PSoft")
Set rS = wS.Cells(1, 1).CurrentRegion
Set rS = rS.Offset(2).Resize(rS.Rows.Count + 2)
vS = rS
Application.ScreenUpdating = False

For i = LBound(vC) To UBound(vC)
For j = LBound(vS) To UBound(vS)
If InStr(1, vS(j, 15), vC(i, 17), 1) And _
InStr(1, vS(j, 14), vC(i, 18), 1) And _
InStr(1, vS(j, 13), vC(i, 4), 1) And _
InStr(1, vS(j, 16), vC(i, 13), 1) Then
wC.Cells(i + 4, 19) = wS.Cells(j + 2, 3)
End If
Next j
Next i

Thanks for letting me know. We both learned something.