Public Function GetARM2(ByRef base As Excel.Range, ByVal city As String, ByVal street As String, ByVal house As String, ByVal house2 As String) As String
    Dim arr As Variant: arr = base.Value2
    Dim j As Long
    For i = 1 To UBound(arr, 1)
        If city Like CStr(arr(i, 1)) _
        And street Like CStr(arr(i, 2)) _
       And house Like CStr(arr(i, 3)) _
       And house2 Like CStr(arr(i, 4)) Then
           j = i
           Exit For
        ElseIf i = UBound(arr, 1) Then
           j = 0
        End If
    Next
    If j > 0 Then
       GetARM2 = arr(i, 6)
    Else
       GetARM2 = "нет такого"
    End If
End Function

Public Function GetARM(ByRef base As Excel.Range, ByVal city As String, ByVal street As String, ByVal house As Double) As String
    Dim arr As Variant: arr = base.Value2
    Dim j As Long
    For i = 1 To UBound(arr, 1)
        If city Like CStr(arr(i, 1)) _
        And street Like CStr(arr(i, 2)) _
        And Fix(house) >= CDbl(Trim(arr(i, 3))) _
        And Fix(house) <= CDbl(Trim(arr(i, 4))) Then
           j = i
           Exit For
        ElseIf i = UBound(arr, 1) Then
           j = 0
        End If
    Next
    If j > 0 Then
       GetARM = arr(i, 6)
    Else
       GetARM = "нет такого"
    End If
End Function

Public Function GetARM2(ByRef base As Excel.Range, ByVal city As String, ByVal street As String, ByVal house As String, ByVal house2 As String) As String
    Dim arr As Variant: arr = base.Value2
    Dim j As Long
    For i = 1 To UBound(arr, 1)
        If city Like CStr(arr(i, 1)) _
        And street Like CStr(arr(i, 2)) _
       And house Like CStr(arr(i, 3)) _
       And house2 Like CStr(arr(i, 4)) Then
           j = i
           Exit For
        ElseIf i = UBound(arr, 1) Then
           j = 0
        End If
    Next
    If j > 0 Then
       GetARM2 = arr(i, 6)
    Else
       GetARM2 = "нет такого"
    End If
End Function

=ЕСЛИ(ПОИСКПОЗ(F6;ИНДЕКС(инвест!$C:$C;ПОИСКПОЗ(2;МУМНОЖ(Ч(D6:E6=инвест!$A:$B);{1:1});0)):ИНДЕКС(инвест!$D:$D;ПОИСКПОЗ(2;МУМНОЖ(Ч(D6:E6=инвест!$A:$B);{1:1});0)))=1;ИНДЕКС(инвест!$F:$F;ПОИСКПОЗ(2;МУМНОЖ(Ч(D6:E6=инвест!$A:$B);{1:1});0));"такого адреса нет")

ActiveCell.FormulaR1C1 = _         "=IF(MATCH(RC[3],INDEX(инвест!C3,MATCH(2,MMULT(N(RC[1]:RC[2]=инвест!C1:C2),{1;1}),0)):INDEX(инвест!C4,MATCH(2,MMULT(N(RC[1]:RC[2]=инвест!C1:C2),{1;1}),0)))=1,INDEX(инвест!C6,MATCH(2,MMULT(N(RC[1]:RC[2]=инвест!C1:C2),{1;1}),0)),""такого адреса нет"")"