Как выполнить поиск по значению (VLOOKUP) и вернуть несколько уникальных значений без дубликатов в Excel?

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

Поиск по значению и возврат нескольких совпадающих уникальных значений без дубликатов в Excel

• С помощью функций TEXTJOIN и FILTER
• С помощью мощной функции
• С помощью пользовательской функции

Возврат нескольких совпадающих уникальных значений без дубликатов с помощью функций TEXTJOIN и FILTER

Если вы используете Excel365 или Excel2021, вы можете легко воспользоваться функциями TEXTJOIN и FILTER. Эти функции позволяют динамически фильтровать данные и объединять результаты в одной ячейке.

Введите следующую формулу в пустую ячейку для вывода результата, затем нажмите клавишу "Enter", чтобы получить все совпадающие значения без дубликатов. Смотрите скриншот:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(C2:C17, A2:A17=E2)))

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

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

Нажмите "Kutools" > "Супер ПОИСК" > "Один-ко-многим поиску (возвращает несколько результатов)", чтобы открыть диалоговое окно "Один-ко-многим поиску", затем выполните следующие действия в диалоговом окне:

1. Отдельно выберите "Область размещения списка" и "Диапазон значений для поиска" в соответствующих полях;
2. Выберите диапазон таблицы, который вы хотите использовать;
3. Укажите ключевой столбец и столбец возврата в выпадающих списках "Ключевой столбец" и "Столбец возврата" соответственно;
4. В завершение нажмите кнопку "OK".
   

Результат:

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

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

возвращать несколько совпадающих уникальных значений даже в более старых версиях Excel.

1. Удерживайте клавиши "Alt" + "F11", чтобы открыть окно "Microsoft Visual Basic for Applications".
2. Нажмите "Вставка" > "Модуль" и вставьте следующий код в окно "Модуль".

   Код VBA: Поиск по значению и возврат нескольких уникальных совпадающих значений:

     Function VlookupUnique(lookupValue As String, lookupRange As Range, resultRange As Range, delim As String) As String
       Dim cell As Range
       Dim result As String
       Dim dict As Object
       Set dict = CreateObject("Scripting.Dictionary")
       For Each cell In lookupRange
           If cell.Value = lookupValue Then
               If Not dict.exists(resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value) Then
                   dict.Add resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value, True
                   result = result & delim & resultRange.Cells(cell.Row - lookupRange.Row + 1, 1).Value
               End If
           End If
       Next cell 
       If Len(result) > 0 Then
           VlookupUnique = Mid(result, Len(delim) + 1)
       Else
           VlookupUnique = ""
       End If
   End Function

3. Сохраните и закройте окно с кодом, вернитесь на лист и введите следующую формулу, затем нажмите клавишу "Enter", чтобы получить нужный результат. Смотрите скриншот:

   =VlookupUnique(E2, A2:A17, C2:C17, ", ")

   
   

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