Note: The other languages of the website are Google-translated. Back to English
Войти  \/ 
x
or
x
Регистрация  \/ 
x

or

Как выполнить vlookup и вернуть несколько значений по вертикали в Excel?

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

Vlookup и вернуть все соответствующие значения по вертикали

Vlookup и вернуть все соответствующие значения по горизонтали

Vlookup и вернуть все соответствующие значения в одну ячейку


Vlookup и вернуть все соответствующие значения по вертикали

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

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

=IFERROR(INDEX($B$2:$B$15, SMALL(IF($D$2=$A$2:$A$15, ROW($A$2:$A$15)-ROW($A$2)+1), ROW(1:1))),"" )

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

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


Vlookup и вернуть все соответствующие значения по горизонтали

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

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

=IFERROR(INDEX($B$2:$B$15,SMALL(IF($E$1=$A$2:$A$15,ROW($A$2:$A$15)-ROW($A$2)+1),COLUMN(A1))),"")

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

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


Vlookup и вернуть все соответствующие значения в одну ячейку

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

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

=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))

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

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

Советы: Эта формула успешно применяется только в 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% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    itika · 1 years ago
    how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
  • To post as a guest, your comment is unpublished.
    Taia · 1 years ago
    After I extend the data, some of the cells is showing a "0" instead of blank
  • To post as a guest, your comment is unpublished.
    Win E · 1 years ago
    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
  • To post as a guest, your comment is unpublished.
    Jeff · 1 years ago
    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