Перейти к содержимому

Выполнение поиска Vlookup и возврат нескольких значений по одному или нескольким критериям

Author: Xiaoyang Last Modified: 2025-08-07
vlookup and return multiple values

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

Выполнение Vlookup и возврат всех соответствующих значений по вертикали

Выполнение Vlookup и возврат всех соответствующих значений по горизонтали

Выполнение Vlookup и возврат всех соответствующих значений в одну ячейку

Выполнение Vlookup и возврат всех соответствующих значений по вертикали

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

1. Введите или скопируйте эту формулу в пустую ячейку, куда вы хотите вывести результат:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Примечание: В приведённой выше формуле C 2:C20 — это столбец, содержащий совпадающие записи, которые вы хотите вернуть; A2:A20 — столбец с критерием; E2 — конкретный критерий, по которому нужно вернуть значения. При необходимости измените их на свои диапазоны.

2. Затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первое значение, после чего протяните маркер заполнения вниз, чтобы получить все соответствующие записи, как показано на скриншоте:

return all matching values vertically based on a specific criterion

Советы:

Чтобы выполнить Vlookup и вернуть все совпадающие значения по более конкретным критериям по вертикали, используйте приведённую ниже формулу и нажмите клавиши Ctrl + Shift + Enter.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Vlookup and return all matching values based on more specific values vertically

a screenshot of kutools for excel ai

Раскройте магию Excel с Kutools AI

  • Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
  • Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
  • Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
  • Интерпретация формул: Легко разбирайтесь в сложных формулах.
  • Перевод текста: Преодолейте языковые барьеры в ваших таблицах.
Улучшите возможности Excel с помощью инструментов на базе ИИ. Скачать сейчас и испытайте беспрецедентную эффективность!

Выполнение Vlookup и возврат всех соответствующих значений по горизонтали

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

1. Введите или скопируйте эту формулу в пустую ячейку, куда вы хотите вывести результат:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Примечание: В приведённой выше формуле C2:C20 — это столбец, содержащий совпадающие записи, которые вы хотите вернуть; A2:A20 — столбец с критерием; F1 — конкретный критерий, по которому нужно вернуть значения. При необходимости измените их на свои диапазоны.

2. Затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить первое значение, после чего протяните маркер заполнения вправо, чтобы получить все соответствующие записи, как показано на скриншоте:

Vlookup and return all corresponding values horizontally by one condition

Советы:

Чтобы выполнить Vlookup и вернуть все совпадающие значения по более конкретным критериям по горизонтали, используйте приведённую ниже формулу и нажмите клавиши Ctrl + Shift + Enter.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Vlookup and return all corresponding values horizontally by multiple criteria

Выполнение Vlookup и возврат всех соответствующих значений в одну ячейку

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

1. Введите или скопируйте приведённую ниже формулу в пустую ячейку:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Примечание: В приведённой выше формуле C2:C20 — это столбец, содержащий совпадающие записи, которые вы хотите вернуть; A2:A20 — столбец с критерием; F1 — конкретный критерий, по которому нужно вернуть значения. При необходимости измените их на свои диапазоны.

2. Затем одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы получить все совпадающие значения в одной ячейке, как показано на скриншоте:

vlookup and return all corresponding values into a single cell by one condition

Советы:

Чтобы выполнить Vlookup и вернуть все совпадающие значения по более конкретным критериям в одной ячейке, используйте приведённую ниже формулу и нажмите клавиши Ctrl + Shift + Enter.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

vlookup and return all corresponding values into a single cell by multiple criteria

Примечание: Эта формула работает только в Excel2016 и более поздних версиях. Если у вас нет Excel2016, просмотрите здесь, чтобы узнать, как это сделать.

Больше статей по теме Vlookup:

  • Vlookup и возврат совпадающих данных между двумя значениями в Excel
  • В Excel можно использовать стандартную функцию Vlookup для получения соответствующего значения на основе заданных данных. Однако иногда требуется выполнить поиск Vlookup и вернуть значение, находящееся между двумя значениями. Как решить эту задачу в Excel?
  • Vlookup и возврат нескольких значений из выпадающего списка
  • В Excel как выполнить поиск Vlookup и вернуть несколько соответствующих значений из выпадающего списка? То есть, когда вы выбираете элемент из списка, все связанные с ним значения отображаются сразу, как показано на скриншоте. В этой статье я пошагово расскажу, как это сделать.
  • Vlookup с возвратом пустой ячейки вместо0 или N/A в Excel
  • Обычно при использовании функции Vlookup для возврата соответствующего значения, если совпадающая ячейка пуста, возвращается0, а если значение не найдено — ошибка #N/A. Как сделать так, чтобы вместо0 или #N/A отображалась пустая ячейка?
  • Vlookup с возвратом нескольких столбцов из таблицы Excel
  • В листе Excel можно использовать функцию Vlookup для возврата совпадающего значения из одного столбца. Однако иногда требуется извлечь совпадающие значения сразу из нескольких столбцов, как показано на скриншоте. Как получить соответствующие значения одновременно из нескольких столбцов с помощью функции Vlookup?
  • Vlookup значений на нескольких листах
  • В Excel мы легко можем использовать функцию Vlookup для возврата совпадающих значений в одной таблице на листе. Но задумывались ли вы, как выполнить поиск значения Vlookup на нескольких листах? Допустим, у меня есть три листа с диапазонами данных, и теперь я хочу получить часть соответствующих значений по критериям из этих трёх листов.

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

  • Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
  • Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
  • Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
  • Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
  • Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
  • Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
  • Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
  • Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...
kte tab 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
  • Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!
officetab bottom