Перейти к основному содержанию

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

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

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

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

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


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

doc vlookup объединить 1

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 вместе, чтобы получить относительную базу значений по определенному критерию, см. снимок экрана:

doc vlookup объединить 2

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

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

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

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

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

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

doc vlookup объединить 3


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

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, все соответствующие значения, основанные на определенных данных, были возвращены в одну ячейку с разделителем пробела, см. снимок экрана:

doc vlookup объединить 4

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


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

Kutools for Excel : с более чем 300 удобными надстройками Excel, бесплатно и без ограничений в течение 30 дней.

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

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

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

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

doc vlookup объединить 6

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

doc vlookup объединить 7

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

doc vlookup объединить 8 2 doc vlookup объединить 9

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


Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Лучшие инструменты для офисной работы

Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

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

вкладка kte 201905


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Is there any way to get the unique "name" for "class1"
This comment was minimized by the moderator on the site
Hello, sym-john,
Maybe the below article can solve your problem, please view it:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
This comment was minimized by the moderator on the site
This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

E.g:

Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
This comment was minimized by the moderator on the site
you would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
This comment was minimized by the moderator on the site
The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
This comment was minimized by the moderator on the site
Hi, jeff,
Thanks for your sharing, you must be a warmhearted man.
This comment was minimized by the moderator on the site
I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

Thank you so much for your help!!
This comment was minimized by the moderator on the site
Is there a way to delete the duplicate values in the concatenate?
This comment was minimized by the moderator on the site
Hello, Jacob,
May be the following article can help you to solve your problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Please try, hope it can help you!
This comment was minimized by the moderator on the site
Is there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
This comment was minimized by the moderator on the site
you can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
End If
End If
Next i
This comment was minimized by the moderator on the site
This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
This comment was minimized by the moderator on the site
Loved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :) Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long) Dim rng As Range Dim xResult As String xResult = "" For Each rng In pWorkRng If rng = pValue Then xResult = xResult & ";" & rng.Offset(0, pIndex - 1) If Left(xResult, 1) = ";" Then xResult = MID(xResult,2,255) End If End If Next CusVlookup = xResult End Function
This comment was minimized by the moderator on the site
Make if condition for result if empty.

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
If Not result = "" Then
result = result & " " & x.Offset(0, indexcol - 1)
Else
result = x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
This comment was minimized by the moderator on the site
When using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
This comment was minimized by the moderator on the site
How to get the result. Please help. data data1 result a 1 a1 b 2 a2 c b1 b2 c1 c2
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations