Dynamic MATCH User Defined Function VBA Code Example

Dynamic Match

A custom function which allows user to perform certain calculations over a range based on a value supplied to be matched. Give code can be modified to fit any formula like AVG, COUNT, COUNTA etc. In below example we have two columns Column “A” contains Ids and column “B” contains Values, the Dynamic Match formula will match Ids and perform calculation over Column “B” to populate values.

Source data

Code example

Public Function DYNAMIC_MATCH(oRangeLookup As Range, oValueToLookup As String, oRangeToBeCalculated As Range, oFormulaToBeApplied As String) As Variant
    DYNAMIC_MATCH = "Dynamic error"
    If Not oRangeLookup Is Nothing Then
        If Not oRangeToBeCalculated Is Nothing Then
            If Len(oValueToLookup) > 0 Then
                If Len(oFormulaToBeApplied) > o Then
                    Dim oRng As Range
                    For Each oRng In oRangeLookup
                        If oRng = oValueToLookup Then
                            'Find value and apply formula
                            Select Case oFormulaToBeApplied
                                Case "SUM":
                                        DYNAMIC_MATCH = Val(DYNAMIC_MATCH) + oRangeToBeCalculated.Cells(oRng.Row, oRng.Column)
                                Case "PRODUCT":
                                    DYNAMIC_MATCH = Val(DYNAMIC_MATCH) * oRangeToBeCalculated.Cells(oRng.Row, oRng.Column)
                                Case "MINUS":
                                    DYNAMIC_MATCH = Val(DYNAMIC_MATCH) - oRangeToBeCalculated.Cells(oRng.Row, oRng.Column)
                                Case Else
                                    DYNAMIC_MATCH = "Unknown Formula"
                            End Select
                        End If
                    Next oRng
                    
                    Set oRng = Nothing
                Else
                    DYNAMIC_MATCH = "Formula error"
                    Exit Function
                End If
            Else
                DYNAMIC_MATCH = "Lookup value error"
                Exit Function
            End If
        Else
            DYNAMIC_MATCH = "Range to be calculated error"
            Exit Function
        End If
    Else
        DYNAMIC_MATCH = "Range Lookup error"
        Exit Function
    End If
End Function

Implementation Example

Output

Share on Facebook

Tweet