Как vlookup и объединить несколько соответствующих значений в Excel?

Как все мы знаем, ВПР Функция в Excel может помочь нам найти значение и вернуть соответствующие данные в другом столбце, но в целом она может получить только первое относительное значение, если есть несколько совпадающих данных. В этой статье я расскажу о том, как выполнить vlookup и объединить несколько соответствующих значений только в одной ячейке или вертикальном списке.

Vlookup и возврат нескольких совпадающих значений по вертикали с формулой

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью функции, определяемой пользователем

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel

Vlookup и возврат нескольких совпадающих значений по вертикали с формулой

Предположим, у меня есть следующий диапазон данных, чтобы получить все соответствующие значения на основе определенного значения по вертикали, как показано на следующем снимке экрана, вы можете применить формулу массива.

1. Введите эту формулу: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") в пустую ячейку, в которую вы хотите поместить результат, например E2, а затем нажмите Shift + Ctrl + Enter вместе, чтобы получить относительную базу значений по определенному критерию, см. снимок экрана:

Внимание: В приведенной выше формуле:

A1: A16 это диапазон столбцов, который содержит конкретное значение, которое вы хотите найти;

D2 указывает конкретное значение, которое вы хотите просмотреть;

B1: B16 это диапазон столбцов, из которого вы хотите вернуть соответствующие данные;

$ 1: $ 16 указывает ссылку на строки в пределах диапазона.

2. Затем выберите ячейку E2 и перетащите дескриптор заполнения вниз к ячейкам, пока не получите пустые ячейки, а все совпадающие значения будут перечислены в столбце, как показано на следующем снимке экрана:

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью функции, определяемой пользователем

Вместо получения относительных значений по вертикали иногда требуется объединить соответствующие значения в одну ячейку и объединить их с помощью определенного разделителя. В этом случае вам может помочь следующая функция, определяемая пользователем.

1. Удерживая нажатой ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модулии вставьте следующий код в Модули Окно.

Код VBA: Vlookup и объединение нескольких совпадающих значений в ячейке

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
    If x = lookupval Then
        result = result & " " & x.Offset(0, indexcol - 1)
    End If
Next x
CusVlookup = result
End Function

3. Затем сохраните и закройте этот код, вернитесь на рабочий лист и введите следующую формулу: = cusvlookup (D2; A1: B16,2) в пустую ячейку, куда вы хотите поместить результат, и нажмите Enter key, все соответствующие значения, основанные на определенных данных, были возвращены в одну ячейку с разделителем пробела, см. снимок экрана:

Внимание: В приведенной выше формуле: D2 указывает значения ячеек, которые вы хотите найти, А1: В16 это диапазон данных, из которого вы хотите получить данные, число 2 это номер столбца, из которого должно быть возвращено совпадающее значение, вы можете изменить эти ссылки по своему усмотрению.

Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel

Если вы только что Kutools for Excel, С его Расширенные ряды комбинирования вы можете быстро и легко завершить эту работу. Эта функция может помочь вам объединить все совпадающие значения с определенными разделителями на основе тех же данных в другом столбце.

После установки Kutools for Excel, пожалуйста, сделайте следующее:

1. Выберите диапазон данных, для которого вы хотите получить соответствующие значения на основе конкретных данных.

2. Затем нажмите Кутулс > Слияние и разделение > Расширенные ряды комбинирования, см. снимок экрана:

3. В Расширенные ряды комбинирования В диалоговом окне щелкните имя столбца, на основе которого вы хотите объединить, затем щелкните Основной ключ кнопку, см. снимок экрана:

4. Затем щелкните имя другого столбца, в котором вы хотите вернуть совпадающие значения, и щелкните Сочетать чтобы выбрать один разделитель для разделения комбинированных значений, см. снимок экрана:

5, Затем нажмите Ok кнопки, все соответствующие значения, основанные на одинаковых значениях, были объединены вместе с определенным разделителем, см. скриншоты:

 Загрузите бесплатную пробную версию Kutools for Excel прямо сейчас!

Демо: Vlookup и объединение нескольких совпадающих значений в ячейке с помощью Kutools for Excel