KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

ВПР и возврат нескольких значений на основе одного или нескольких критериев

АвторСяоянДата изменения
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))),"" )

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

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

возврат всех совпадающих значений вертикально на основе определённого критерия

Советы:

Чтобы выполнить поиск с помощью ВПР и вертикально вернуть все совпадающие значения на основе более точных условий, используйте приведённую ниже формулу и нажмите клавиши 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 и возврат всех совпадающих значений на основе более конкретных значений вертикально

снимок экрана kutools for excel ai

Раскройте магию Excel с помощью KUTOOLS AI

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

ВПР и возврат всех соответствующих значений горизонтально

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

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 и возврат всех соответствующих значений горизонтально по одному условию

Советы:

Чтобы выполнить поиск с помощью ВПР и горизонтально вернуть все совпадающие значения на основе более точных условий, примените приведённую ниже формулу и нажмите клавиши 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 и возврат всех соответствующих значений горизонтально по нескольким критериям

ВПР и возврат всех соответствующих значений в одну ячейку

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

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

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

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

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

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

Советы:

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

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

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

Примечание:Эта формула успешно работает только в Excel 2016 и более поздних версиях. Если у вас нет Excel 2016, перейдите сюда, чтобы загрузить его.

Другие статьи по теме ВПР:

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

Лучшие инструменты для повышения продуктивности в офисе

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

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