Multiple criterias in a VLOOKUP in Excel VBA I have made the following custom defined function in Excel. It works somehow like VLOOKUP(), but it takes two criteria. I think the code is a bit of a mess. Does anyone has any comments, suggestions, or improvements?

Public Function VLOOKUPMC(ByVal return_col_num As Long, ByRef table_array_1 As Range, ByVal lookup_value_1 As Variant, ByRef table_array_2 As Range, ByVal lookup_value_2 As Variant) As Variant
    Dim rCell1 As Range

    For Each rCell1 In table_array_1
        With rCell1
            If .Value = lookup_value_1 Then
                If .Offset(0, table_array_2.Column - .Column) = lookup_value_2 Then
                    VLOOKUPMC = .Offset(0, return_col_num - .Column)
                    Exit Function
                End If
            End If
        End With
    Next rCell1
    VLOOKUPMC = CVErr(xlErrNA)
End Function
Here's a different version, though I won't claim that it is significantly better. The code seemed to work just fine and was mostly easy to understand (see my comment on the original question). My only big suggestion would be to add some comments to better describe what you are doing and how this works.

For a brief description of my changes, see the comments in the code. I can elaborate more if necessary.

Public Function VLOOKUPMC(ByVal return_col_num As Long, _
    ByRef table_array_1 As Range, ByVal lookup_value_1 As Variant, _
    ByRef search_column_2 As Long, ByVal lookup_value_2 As Variant) As Variant
    'Changed table_array_2 to search_column_2 because that's all that was used in the code below.
    Dim rCell1 As Range
    VLOOKUPMC = CVErr(xlErrNA)
    'Left the For loop as-is, but maybe you want it to look only in the first column, like the normal VLOOKUP would?
    For Each rCell1 In table_array_1
        'Modified logic to all fit on one line/remove nesting. 
        'This organization may not be preferred, but it's one way to clean up the so-called 'mess'.
        VLOOKUPMC = _
            IIf(rCell1.Value = lookup_value_1 And _
            rCell1.Offset(0, search_column_2 - rCell1.Column) = lookup_value_2, _
            rCell1.Offset(0, return_col_num - rCell1.Column), _
            VLOOKUPMC)
        If VarType(VLOOKUPMC) &lt;&gt; vbError Then Exit Function
    Next rCell1
End Function