Excel vlookup с несколькими результатами

Ответ 1

Вот функция, которая сделает это за вас. Это немного отличается от Vlookup тем, что вы укажете только столбец поиска, а не весь диапазон, затем в качестве третьего параметра вы скажете, сколько столбцов осталось слева (отрицательные числа) или справа (положительно), чтобы получить ваше возвращаемое значение.

Я также добавил вариант использования разделителя, в вашем случае вы будете использовать "". Вот вызов функции для вас, предполагая, что первая строка с номером Acct равна A, а результатом является строка B:

=vlookupall("0001", A:A, 1, " ")

Вот функция:

Function VLookupAll(ByVal lookup_value As String, _
                    ByVal lookup_column As range, _
                    ByVal return_value_column As Long, _
                    Optional seperator As String = ", ") As String

Dim i As Long
Dim result As String

For i = 1 To lookup_column.Rows.count
    If Len(lookup_column(i, 1).text) <> 0 Then
        If lookup_column(i, 1).text = lookup_value Then
            result = result & (lookup_column(i).offset(0, return_value_column).text & seperator)
        End If
    End If
Next

If Len(result) <> 0 Then
    result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

Примечания:

• Я сделал "," разделитель по умолчанию для результатов, если вы его не вводите.
• Если есть один или несколько ударов, я добавил некоторые проверки в конце убедитесь, что строка не заканчивается дополнительным разделителем.
• Я использовал A: A как диапазон, так как я не знаю ваш диапазон, но очевидно, это быстрее, если вы введете фактический диапазон.

Ответ 2

Один из способов сделать это - использовать формулу массива для заполнения всех совпадений в скрытом столбце и затем объединить эти значения в строку для отображения:

=IFERROR(INDEX(cropTypeValues,SMALL(IF(accLookup=accNumValues,ROW(accNumValues)-MIN(ROW(accNumValues))+1,""),ROW(A1))),"")

• cropTypeValues ​​. Именованный диапазон, содержащий список ваших типов обрезков.
• accLookup. Именованный диапазон, содержащий номер учетной записи для поиска.
• accNumValues ​​. Именованный диапазон, содержащий список вашей учетной записи числа.

Введите в виде формулы массива (Ctrl + Shift + Enter), а затем скопируйте ее по мере необходимости.

Сообщите мне, нужна ли вам какая-либо часть формулы, поясняющей.

Ответ 3

У меня была аналогичная проблема, и я давно искал похожие решения, но меня это ничуть не убеждало. Либо вам приходилось писать макрос, либо какую-то специальную функцию, хотя для моих нужд самым простым решением является использование сводной таблицы, например. Excel.

Если вы создаете новую сводную таблицу из своих данных и сначала добавляете "Acct No" в качестве метки строки, а затем добавляете "CropType" в качестве RowLabel, у вас будет очень хорошая группировка, которая перечисляет для каждой учетной записи все типы обрезки. Однако он не будет делать это в одной камере.

Ответ 4

Вот мой код, который даже лучше, чем excel vlookup, потому что вы можете выбрать критерий colum, и, безусловно, разделитель (Carriege return тоже)...

Function Lookup_concat(source As String, tableau As Range, separator As String, colSRC As Integer, colDST As Integer) As String
    Dim i, y As Integer
    Dim result As String

    If separator = "CRLF" Then
        separator = Chr(10)
    End If

    y = tableau.Rows.Count
    result = ""
    For i = 1 To y
        If (tableau.Cells(i, colSRC) = source) Then
            If result = "" Then
                result = tableau.Cells(i, colDST)
            Else
                result = result & separator & tableau.Cells(i, colDST)
            End If
        End If
    Next
    Lookup_concat = result
End Function

И в подарок вы можете также выполнить поиск по нескольким элементам одной и той же ячейки (на основе того же разделителя). Действительно полезно

Function Concat_Lookup(source As String, tableau As Range, separator As String, colSRC As Integer, colDST As Integer) As String
    Dim i, y As Integer
    Dim result As String

    Dim Splitted As Variant

    If separator = "CRLF" Then
        separator = Chr(10)
    End If

    Splitted = split(source, separator)

    y = tableau.Rows.Count
    result = ""
    For i = 1 To y
        For Each word In Splitted
            If (tableau.Cells(i, colSRC) = word) Then
                If result = "" Then
                    result = tableau.Cells(i, colDST)
                Else
                    Dim Splitted1 As Variant
                    Splitted1 = split(result, separator)
                    If IsInArray(tableau.Cells(i, colDST), Splitted1) = False Then
                        result = result & separator & tableau.Cells(i, colDST)
                    End If
                End If
            End If
        Next
    Next
    Concat_Lookup = result
End Function

Предыдущий элемент sub нуждается в этой функции

Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Ответ 5

Function VLookupAll(vValue, rngAll As Range, iCol As Integer, Optional sSep As String = ", ")
    Dim rCell As Range
    Dim rng As Range
    On Error GoTo ErrHandler
    Set rng = Intersect(rngAll, rngAll.Columns(1))
    For Each rCell In rng
        If rCell.Value = vValue Then
            VLookupAll = VLookupAll & sSep & rCell.Offset(0, iCol - 1).Value
        End If
    Next rCell
    If VLookupAll = "" Then
        VLookupAll = CVErr(xlErrNA)
    Else
        VLookupAll = Right(VLookupAll, Len(VLookupAll) - Len(sSep))
    End If
ErrHandler:
    If Err.Number <> 0 Then VLookupAll = CVErr(xlErrValue)
End Function

Используйте это:

=VLookupAll(K1, A1:C25, 3)

чтобы просмотреть все вхождения значения K1 в диапазоне A1: A25 и вернуть соответствующие значения из столбца C, разделенные запятыми.

Если вы хотите суммировать значения, вы можете использовать SUMIF, например

=SUMIF(A1:A25, K1, C1:C25)

чтобы суммировать значения в C1: C25, где соответствующие значения в столбце A равны значению K1.

ВСЕ D BEST.