Альтернатива VLOOKUP с качественными qualfiers & rdquo; (т. е. текст и т. д.) [dубликат]

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

a. Вспомогательный столбец:

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

=A1&B1

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

[ ! d14]

Затем используйте INDEX / MATCH:

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

[!d6]

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

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

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

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

[!d6]

Если ваш набор данных регулярно меняет размеры, мы можем изменить приведенное выше динамическое значение, добавив больше 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))

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

Описанные выше методы устанавливаются в порядке наилучшего наилучшего.

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

a. Если у вас есть 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 будет помещать {} в формулу.

Как и формула AGGREGATE, она должна быть ограничена набором данных. Диапазоны могут быть сделаны динамическими с помощью функций INDEX / MATCH, таких как выше.

b. Если у вас нет 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 (), как описано выше.