Formula to Find Match Using Wildcard and Multiple Criteria

Question

I'm trying to make a formula with multiple criteria and wildcards that will return the matching category if found. I think this would be done through a robust Index-Match formula but I'm struggling to get all the criteria into a working formula. I'll explain better with examples...

I have two worksheets, sheet "Import Data" and sheet "Invoice Coding". The formula would be entered on sheet "Import Data", in any column off to the side, say formula goes into Column AD.

Here is the "Import Data" sheet:

Here is the "Invoicing Coding" sheet (there are over 500 rows and growing, so this is a small screenshot):

Step one would be to look for the "ACCT" number (Column O) from "Import Sheet" against the "Invoicing Coding" sheet, which would usually have multiple matches. Step two would then check the corresponding "INV" on the "Import Data" sheet and see if any of the wildcard invoices on sheet "Invoice Coding" match. The return would be the "Category" (Column D) from the "Invoice Coding" sheet.

I'll provide a specific example to try to explain better:

On this line, I want to use column O data, which is the number 50000.

Lookup 50000 on the "Invoice Coding" sheet, which has many results.

Then use the INV from Column D on the previous screenshot, which is...

To find if it has a partial match to the wildcards listed in Column C of the "Invoice Coding" sheet screenshot just above.

So in this specific example, the ACCT and INV from the "Import Data" sheet match row 178 on the "Invoice Coding" sheet. The expected result from the formula would be the Category from Column D; Third Party.

Here is what I have for a formula so far, which does not incorporate the partial invoice match using wildcards:

=INDEX('Invoice Coding'!A2:E514,MATCH('Import Data'!O2,'Invoice Coding'!A2:A514,0),4)

This technically returns a Category but it doesn't use the INV # vs the partial INV with wildcard, so the return may be incorrect.

I hope my explanation makes sense. Any advice on if I can enhance an Index-Match formula to include all the required criteria lookups?

As a side note, I would then repeat this formula with slight tweaks to also return the Sub-Category from Column E of the "Invoice Coding" sheet.

Thank you all so much in advance!

Answer 1

going to answer my own question. We found a solution that works for us through VBA. Originally I was hesitant to approach with VBA because I thought it would take much longer to run compared to a formula, but our solution is quicker than expected, taking about 30 seconds to run through 60,000 rows.

So, our VBA solution:

'***** Declare variables to be used *****
Dim ImportWS As Worksheet, ilastrow As Long, ilooper As Long, FindArray As Variant, CodingWS As Worksheet, clastrow As Long, CodeArray As Variant, clooper As Long, acct As Long, Inv As String, matchfound As Boolean

Application.ScreenUpdating = False
Application.DisplayAlerts = False
    
    'Import Data sheet prep and storing in FindArray
    Set ImportWS = ThisWorkbook.Sheets("Import Data")
    ilastrow = ImportWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ImportWS.Range("AC1") = "Category"
    ImportWS.Range("AD1") = "Sub Category"
    ImportWS.Range("AE1") = "Billing Name"
    ImportWS.Range("AC2:AE" & ilastrow).ClearContents
    FindArray = ImportWS.Range("AC1:AE" & ilastrow)

    'Storing Coding sheet data into array
    Set CodingWS = ThisWorkbook.Sheets("Invoice Coding")
    clastrow = CodingWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    CodeArray = CodingWS.Range("A1:F" & clastrow)
    
    With ImportWS
        
        'Looping through all rows on Import Data sheet...
        For ilooper = 2 To ilastrow
            
            'Storing GL Acct# and Invoice# for comparison against Coding sheet
            acct = Val(.Range("O" & ilooper))
            Inv = .Range("D" & ilooper)
            
            'matchfound is a boolean that is reset on each row.
            'if a match is found against the Coding sheet it is flipped to true...
            'we stop looking in the following loop...
            'and we commit the cat and subcat to the FindArray variant.
            matchfound = False
            
            'This is our looper for going through the coding table in the following loop.
            clooper = 2
            
            'While we haven't found a match for GL# and Inv...
            'and we haven't reached the end of the table...
            While matchfound = False And clooper <= clastrow
    
                'If acct# matches coding table we perform another check...
                If Val(CodeArray(clooper, 1)) = acct Then
                        
                    '...for partial match on Inv - which accounts for wildcard placement as on the table...

                    If Inv Like CodeArray(clooper, 3) Then
                       'If match is found, matchfound is true, which will stop the next iteration of the loop
                        matchfound = True
                        'and we store the cat and subcat in our FindArray
                        FindArray(ilooper, 1) = CodeArray(clooper, 4)
                        FindArray(ilooper, 2) = CodeArray(clooper, 5)
                        FindArray(ilooper, 3) = CodeArray(clooper, 6)
                    End If
                End If
                
                'If no match, check the next row on the Coding table.
                clooper = clooper + 1
    
            Wend
            
            
        'After we found a match or reached the end of the coding table...
        '...we move on to the next row on the Import Data sheet.
        Next ilooper
        
        'After looping through all rows on the Import Data sheet we commit what we found to columns AC, AD, AE.
        ImportWS.Range("AC1:AE" & ilastrow).Value = FindArray
    
    End With
    
Application.ScreenUpdating = True
Application.DisplayAlerts = True