Vlookup использует 2 столбца для ссылки на другой

Много способов:

Если у вас есть новые формулы динамического массива:

=FILTER(C:C,(A:A=J1)*(B:B=J2))

Если нет, то:

1. Работа с Number возвращает:

Если ваши возвращаемые значения являются числами и совпадение уникально (в данных есть только один Джон Доу) или вы хотите просуммировать возвращаемые значения, если они кратны, то использование СУММЕСЛИМН является самым быстрым методом.

=SUMIFS(C:C,A:A,J1,B:B,J2)

2. С нечисловым возвратом

Если возвращаемые значения не являются числовыми или они кратны, то есть два метода для получения первого совпадения в списке:

а. Вспомогательная колонка:

В четвертом столбце поместите следующую формулу:

=A1&B1

и скопируйте список

Затем используйте ИНДЕКС / ПОИСКПОЗ:

=INDEX(C:C,MATCH(J1&J2,D:D,0))

б. Формула массива:

Если вы не хотите или не можете создать четвертый столбец, используйте формулу типа массива:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:$A$4)/(($A$1:$A$4=J1)*($B$1:$B$4=J2)),1))

Формулы типа массива должны ограничивать размер данных набором данных.

Если ваш набор данных регулярно меняет размеры, мы можем изменить приведенное выше, чтобы оно было динамическим, добавив больше INDEX / MATCH, чтобы вернуть последнюю ячейку с данными:

=INDEX(C:C,AGGREGATE(15,6,ROW($A$1:INDEX($A:$A,MATCH("ZZZ",A:A)))/(($A$1:INDEX($A:$A,MATCH("ZZZ",A:A))=J1)*($B$1:INDEX($B:$B,MATCH("ZZZ",A:A))=J2)),1))

Это позволит набору данных увеличиваться или уменьшаться, а формула будет перебирать только те, у которых есть данные, а не весь столбец.

Описанные выше методы расположены в порядке Лучшее-Лучшее-Хорошее.

3. Чтобы получить несколько ответов в одной ячейке

Если вы не хотите суммировать или возвращаемые значения являются текстовыми и существует несколько экземпляров John Doe, и вы хотите, чтобы все значения возвращались в одной ячейке, тогда:

а. Если у вас есть Office 365 Excel, вы можете использовать форму массива TEXTJOIN:

=TEXTJOIN(",",TRUE,IF(($A$1:$A$4=J1)*($B$1:$B$4=J2),$C$1:$C$4,""))

Поскольку это формула массива, ее необходимо подтвердить с помощью Ctrl-Shift-Enter вместо Enter при выходе из режима редактирования. Если все сделано правильно, Excel поместит вокруг формулы {}.

Как и приведенная выше формула СОВМЕСТНАЯ, она должна быть ограничена набором данных. Диапазоны можно сделать динамическими с помощью функций ИНДЕКС / ПОИСКПОЗ, как указано выше.

б. Если у вас нет Office 365 Excel, добавьте этот код в модуль, прикрепленный к книге:

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0

    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

Затем используйте формулу TEXTJOIN (), как описано выше.

Это альтернатива с использованием VBA в UDF

На основании этого ответа

Это общая функция, определяемая пользователем (UDF)

Шаги

1. Добавьте свои данные в Структурированная таблица Excel

2. Добавить эту функцию в модуль

Код:

Public Function lookupValues(ByVal table As Range, ByVal criteria1_header As String, ByVal lookup_criteria1 As String, ByVal criteria2_header As String, ByVal lookup_criteria2 As String, ByVal return_header As String) As Variant
       
    On Error GoTo CleanFail
    
    ' Get criteria 1 column number from headers
    Dim criteria1Column As Long
    criteria1Column = Application.Match(criteria1_header, table.ListObject.HeaderRowRange, False)
    
    ' Get criteria 2 column number from headers
    Dim criteria2Column As Long
    criteria2Column = Application.Match(criteria2_header, table.ListObject.HeaderRowRange, False)
    
    ' Get value column number from headers according to function parameter
    Dim returnColumn As Long
    returnColumn = Application.Match(return_header, table.ListObject.HeaderRowRange, False)
    
    
    ' Get criteria 1 column values into 1d array
    Dim criteria1Values As Variant
    criteria1Values = WorksheetFunction.Transpose(Application.Index(table.Columns(criteria1Column), 0, 1))
    
    ' Get criteria 2 column values into 1d array
    Dim criteria2Values As Variant
    criteria2Values = WorksheetFunction.Transpose(Application.Index(table.Columns(criteria2Column), 0, 1))
    
    ' Define and redimension an array to hold the concatenated criteria 1 and criteria 2 values
    Dim criteria1_2Values() As Variant
    ReDim criteria1_2Values(1 To UBound(criteria1Values))
    
    ' Concatenate the criteria 1 and criteria 2 values and store them in an array
    Dim counter As Long
    For counter = 1 To UBound(criteria1Values)
        criteria1_2Values(counter) = criteria1Values(counter) & "|" & criteria2Values(counter)
    Next counter
    
    ' Get the matching row according to lookup values
    Dim resultRow As Variant
    resultRow = Application.Match(lookup_criteria1 & "|" & lookup_criteria2, criteria1_2Values, False)
    
    ' Get the result value according to the value column number
    Dim result As Variant
    result = Application.Index(table.Columns(returnColumn), resultRow)
    
    ' Return the value
CleanExit:
    lookupValues = result
    Exit Function
    
CleanFail:
    result = "Check function parameters"
    GoTo CleanExit
End Function

3. Вызовите функцию так:

=lookupValues(TableName;E1;F1;E2;F2;E3)