Excel VBA: checking if all Vlookup() and Match() are using exact match

Excel's MATCH() and VLOOKUP() functions are powerful tools for finding specific data in a worksheet. However, ensuring these functions are used correctly is crucial for accurate results. One common mistake is forgetting to specify an exact match, which can lead to incorrect data retrieval. To address this issue, here is a VBA script that can be used to check all MATCH() and VLOOKUP() formulas in a workbook and identify those that do not specify 0 as the last argument for an exact match.

'------------------------------------------------------------------------- 
' Source: risksir.com 
' Description: Script checking all sheets in the workbook and finds 
  if there are any MATCH() or VLOOKUP() without zero as the last argument 
  (without an exact match)
'------------------------------------------------------------------------- 
Sub ExactMatchCheck()
    Call FindNestedMATCHFunctions("MATCH")
    Call FindNestedMATCHFunctions("VLOOKUP")
End Sub




Sub FindNestedMATCHFunctions(func As Variant)
AccelerateExcel
    
    Debug.Print Now()
    Dim ws As Worksheet
    Dim cell As Range
    Dim formula As String
    Dim matchArray() As String
    Dim matchIndex As Integer
    Dim startPos As Integer
    Dim endPos As Integer
    Dim nestedFormula As String
    Dim nestedMatches() As String
    Dim i As Integer
    
    ReDim matchArray(1 To 1)
    matchIndex = 1
    
    For Each ws In ThisWorkbook.Worksheets
        For Each cell In ws.UsedRange
            If InStr(1, cell.formula, func & "(", vbTextCompare) > 0 Then
                formula = cell.formula
                startPos = InStr(1, formula, func & "(", vbTextCompare)
                Do While startPos > 0
                    endPos = FindClosingParenthesis(formula, startPos + 1)
                    nestedFormula = Mid(formula, startPos, endPos - startPos + 1)
                    Right2 = Right(nestedFormula, 2)
                    If Right2 <> "0)" Then
                        ReDim Preserve matchArray(1 To UBound(matchArray) + 1)
                        matchArray(UBound(matchArray)) = ws.Name & " —> " & cell.Address & " —> " & nestedFormula
                    End If
                    startPos = InStr(endPos + 1, formula, func & "(", vbTextCompare)
                Loop
            End If
        Next cell
    Next ws
    
    ' Print the results to the Immediate Window
    Debug.Print func & " formulas was checked!"
    For i = 1 To UBound(matchArray)
        Debug.Print matchArray(i)
    Next i
    If UBound(matchArray) > 1 Then
        MsgBox "There are " & func & " formulas without exact match. Check Debug.Print box for details.", vbExclamation
    Else
        MsgBox "SUCCESS! No " & func & " formulas without exact match were found."
    End If
disAccelerateExcel
End Sub




Function FindClosingParenthesis(s As String, startPos As Integer) As Integer
    Dim i As Integer
    Dim openParenthesisCount As Integer
    Dim closeParenthesisCount As Integer
    
    openParenthesisCount = 0
    closeParenthesisCount = 0
    
    For i = startPos To Len(s)
        If Mid(s, i, 1) = "(" Then
            openParenthesisCount = openParenthesisCount + 1
        ElseIf Mid(s, i, 1) = ")" Then
            closeParenthesisCount = closeParenthesisCount + 1
            If closeParenthesisCount = openParenthesisCount Then
                FindClosingParenthesis = i
                Exit Function
            End If
        End If
    Next i
End Function