Multiple criterias in a VLOOKUP in Excel VBA
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