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

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

Обычно вы можете использовать функцию 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))),"" )

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

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

Советы:

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

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

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, Затем нажмите Shift + Ctrl + Enter вместе, чтобы получить первое значение, а затем перетащите маркер заполнения вправо, чтобы получить все необходимые записи, как вам нужно, см. снимок экрана:

Советы:

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

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

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

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

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

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

Советы:

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

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

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

Более относительные статьи о Vlookup:

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

  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы и хранение данных; Разделить содержимое ячеек; Объедините повторяющиеся строки и сумму / среднее значение... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Избранные и быстро вставляйте формулы, Диапазоны, диаграммы и изображения; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Группировка сводной таблицы по номер недели, день недели и другое ... Показать разблокированные, заблокированные ячейки разными цветами; Выделите ячейки, у которых есть формула / имя...
вкладка kte 201905
  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Comments (8)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations