Note: The other languages of the website are Google-translated. Back to English

Как выполнить ВПР и вернуть несколько соответствующих значений по горизонтали в Excel?

doc-vlookup-горизонтально-1
По умолчанию функция VLOOKUP может возвращать несколько соответствующих значений на вертикальном уровне в Excel, в некоторых случаях вы можете захотеть вернуть несколько значений на горизонтальном уровне, как показано ниже. Я расскажу вам формулу, которая может решить эту задачу.
ВПР и возврат нескольких значений по горизонтали

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

Например, у вас есть ряд данных, как показано на скриншоте ниже, и вы хотите ВПР для цен Apple.
doc-vlookup-горизонтально-2

1. Выберите ячейку и введите эту формулу. =INDEX($B$2:$B$9, SMALL(IF($A$11=$A$2:$A$9, ROW($A$2:$A$9)-ROW($A$2)+1), COLUMN(A1))) в него, а затем нажмите Shift + Ctrl + Enter и перетащите маркер автозаполнения вправо, чтобы применить эту формулу, пока #NUM! появляется. Смотрите скриншот:
doc-vlookup-горизонтально-3

2. Затем удалите # ЧИСЛО !. Смотрите скриншот:
doc-vlookup-горизонтально-4

Наконечник: В приведенной выше формуле B2: B9 - это диапазон столбцов, в котором вы хотите вернуть значения, A2: A9 - это диапазон столбцов, в котором находится значение подстановки, A11 - это значение подстановки, A1 - первая ячейка вашего диапазона данных. , A2 - первая ячейка диапазона столбцов, в котором находится значение поиска.

Если вы хотите вернуть несколько значений по вертикали, вы можете прочитать эту статью Как найти значение для возврата нескольких соответствующих значений в Excel?


Легко объединяйте несколько листов / книгу в один лист или рабочую книгу

Объединение нескольких листов или книг в один лист или книгу может быть удобно в Excel, но с Сочетать Функция в Kutools for Excel, вы можете объединить десятки листов / книг в один лист или книгу, а также вы можете объединить листы в один только несколькими щелчками мыши.  Нажмите для полнофункционального 30 дней бесплатного пробного периода!
объединить листы
 
Kutools for Excel: с более чем удобными надстройками Excel 300, которые можно попробовать бесплатно без ограничений в 30 дней.


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

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

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (19)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
У меня есть электронная таблица со 115 именами клиентов, значения которых я каждый день перетаскиваю с одной вкладки на другую, используя vlookup. Другой человек в офисе обновляет значения каждый день, вставляя новые значения вместе с соответствующими именами клиентов. Имена клиентов вставляются на вкладке «PVAL» в столбец A, а значения клиентов вставляются в столбец B. На вкладке с именем Master мы запускаем vlookup для имен клиентов, начиная с ячейки N6 (соответствующее имя клиента указано в ячейке AB6). . vlookup = VLOOKUP(AB6,PortDescPortVal,2,FALSE) Каждый день мы обновляем электронную таблицу, в ней есть те же 10 имен клиентов, которые не будут обновляться автоматически. Я должен каждый день заходить на вкладку PVAL и повторно вводить имена клиентов для этих 10, а затем данные будут обновляться. Для остальных клиентов работает каждый день. Также обратите внимание, что человек, который обновляет данные, пытался повторно ввести свои имена для меня, но по какой-то причине, когда она повторно набирает их имена, поиск по-прежнему не работает. Я не могу найти настройку в ее Excel, которая отличается от моей. Я убедился, что все имена клиентов находятся в массиве. Я не могу понять, в чем здесь проблема и почему мне приходится каждый день перепечатывать 10 из 115 имен клиентов, чтобы поиск работал. ПОМОЩЬ!
Этот комментарий был сведен к минимуму модератором на сайте
Попробуйте добавить столбцы (измените свой диапазон ss и избавьтесь от аргумента False следующим образом; =VLOOKUP(AB6,'PortDescPortVal'!A:AA,2,)

Возможно, Обновить все на вкладке «Данные»
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за ваши советы, у меня похожая проблема, но другая. Множественные значения Vlookup - возвращайте НЕСКОЛЬКО соответствующих значений для НЕСКОЛЬКИХ значений поиска. Формула, которую я ищу, ищет слова в текстовых строках, начиная с A1, затем добавляет категории из большого списка категорий в таблицу на ДРУГОМ РАБОЧЕМ ТАБЛИЦЕ «Списки динамических категорий», в зависимости на слова, найденные в строке A1. Это формула в B1. Объем данных огромен — 19,000 1 текстовых строк в строке A. Например, текстовая строка может быть следующей: A B C 2 собака имеет черные пятна Далматин 1 собака высокая Большая собака Моя формула ищет «черные пятна» и возвращает «далматинец» в B2 My формула ищет «собака высокая» - моя формула ищет «высокий» и возвращает «большие собаки» в B1. Формула в B1: =PROPER(IFERROR(LOOKUP(100E+1,SEARCH('Динамические списки категорий'!$A$1000) :$A$1,A1),'Списки динамических категорий'!$A$1000:$A$1),"")) 'Списки динамических категорий' (ДРУГОЙ РАБОЧИЙ ТАБЛИЦ) A B 2 Поиск по слову для поиска категорий: Список Вставить 3 черные точки Далматин 4 высокая большая собака 5 короткая маленькая собака 1000 -1000 БОЛЬШЕ -2 БОЛЬШЕ Моя проблема в том, что мне нужно найти 3-е, 4-е, 1-е вхождения Пример A B C D 2 собака с черными пятнами Далматин 3 собака высокая Большая собака 4 4 собака с черными пятнами и is tal Dalmation Large Dog A3 "собака с черными пятнами и высокая" Я хочу, чтобы формула вернула "Далматин" и "большая собака" в BXNUMX Любая помощь будет оценена по достоинству. Перерыл кучу тем так и не нашел ответа!
Этот комментарий был сведен к минимуму модератором на сайте
У меня похожая проблема, но другая. Множественные значения Vlookup - возвращайте НЕСКОЛЬКО соответствующих значений для НЕСКОЛЬКИХ значений поиска. Формула, которую я ищу, ищет слова в текстовых строках, начиная с A1, затем добавляет категории из большого списка категорий в таблицу на ДРУГОМ РАБОЧЕМ ТАБЛИЦЕ «Списки динамических категорий», в зависимости на слова, найденные в строке A1. Это формула в B1. Объем данных огромен — 19,000 1 текстовых строк в строке A. Например, текстовая строка может быть следующей: A B C 2 собака имеет черные пятна Далматин 1 собака высокая Большая собака Моя формула ищет «черные пятна» и возвращает «далматинец» в B2 My формула ищет «собака высокая» - моя формула ищет «высокий» и возвращает «большие собаки» в B1. Формула в B1: =PROPER(IFERROR(LOOKUP(100E+1,SEARCH('Динамические списки категорий'!$A$1000) :$A$1,A1),'Списки динамических категорий'!$A$1000:$A$1),"")) 'Списки динамических категорий' (ДРУГОЙ РАБОЧИЙ ТАБЛИЦ) A B 2 Поиск по слову для поиска категорий: Список Вставить 3 черные точки Далматин 4 высокая большая собака 5 короткая маленькая собака 1000 -1000 БОЛЬШЕ -2 БОЛЬШЕ Моя проблема в том, что мне нужно найти 3-е, 4-е, 1-е вхождения Пример A B C D 2 собака с черными пятнами Далматин 3 собака высокая Большая собака 4 4 собака с черными пятнами и is tal Dalmation Large Dog A3 "собака с черными пятнами и высокая" Я хочу, чтобы формула вернула "Далматин" и "большая собака" в BXNUMX Любая помощь будет оценена по достоинству. Перерыл кучу тем так и не нашел ответа!
Этот комментарий был сведен к минимуму модератором на сайте
Просто краткое примечание для всех, если вы измените его на: =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$9, МАЛЕНЬКИЙ(ЕСЛИ($A$11=$A$2:$A$9, ROW($A$2:$ A$9)-ROW($A$2)+1), COLUMN(A1))),"None") #NUM станет равным none или используйте "" для получения пустой ячейки! Выглядит намного аккуратнее!
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я набираю формулу, и Excel выдает ошибку, говорящую о том, что в формуле недостаточно аргументов, чем помочь?
Этот комментарий был сведен к минимуму модератором на сайте
Как перечислить все значения (например, список купленных продуктов), вызванные определенным значением (человеком X), используя VLOOK_UP и другие возможные формулы
Этот комментарий был сведен к минимуму модератором на сайте
Вы имеете в виду, что для того, чтобы составить список всех продуктов, которые нужно купить человеку, верно? Если это так, вы можете обратиться к формуле в этой статье и изменить аргументы по мере необходимости, как показано ниже.
Этот комментарий был сведен к минимуму модератором на сайте
Хотя я ищу свои значения на другом листе (я не думаю, что это должно вызывать проблемы?), мне кажется, что я успешно перетаскиваю вправо, только если я захожу и меняю +1 на «0» для 1-й экземпляр идентификатора поиска, «1» для 2-го экземпляра и так далее. Я знаю, сколько экземпляров идентификатора поиска у меня есть, поэтому я могу найти обходной путь. Что мне нужно изменить, чтобы я мог успешно перетаскивать fx по горизонтали без необходимости редактирования (например, чтобы #NUM отображался, когда больше нет значений поиска для возврата).
Этот комментарий был сведен к минимуму модератором на сайте
Извините, я не нашел решения ваших проблем. Если вы нашли это, не могли бы вы сказать мне? Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
Изменить столбец на строку в конце формулы

"=INDEX($B$1:$B$206, SMALL(IF($A$209=$A$1:$A$206, ROW($A$1:$A$206)-ROW($A$1)+1), ROW(A1)))"
Этот комментарий был сведен к минимуму модератором на сайте
Привет. У меня есть три таблицы Google. Первый лист называется «Сводка». Это таблица, содержащая имя нашего сотрудника и количество оценок за неделю от 1 до 7. Следующий лист называется «Оценка чата», а третий лист называется «Оценка электронной почты». Что я хочу сделать, так это то, что когда мы вводим оценочный балл одному из наших сотрудников, он будет автоматически записан в «Сводном листе» как оценка 1 или 2 и так далее и тому подобное. Короче говоря, я хочу получить несколько возвращаемых значений в одном ключе поиска на нескольких листах. Я надеюсь, что вы можете помочь. https://docs.google.com/spreadsheets/d/1lt-e4MxddUKg5xDt_0YchBiEgGe4mFKB-dHENwwtA6Y/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1lt-e4MxddUKg5xDt_0YchBiEgGe4mFKB-dHENwwtA6Y/edit?usp=sharing
Этот комментарий был сведен к минимуму модератором на сайте
Сначала мне нужно найти значение по вертикали, в значении по вертикали больше значений по горизонтали, и мне нужно выбрать большее значение, которое у меня есть. какая функция или формула может помочь
Этот комментарий был сведен к минимуму модератором на сайте
что вы хотите поставить цену в порядке наибольшего количества из столбца c?
Этот комментарий был сведен к минимуму модератором на сайте
Привет,

J'ai retransposé мон проблема взыскания данс ле мемов позиций де колонны дие l'example и др j'obtiens ле сообщение #VALEUR! dès la première case.

Mon excel fonctionne en francais, j'ai tout retranscrit en francais, passé ";"au lieu de",", appuyé sur CTRL+MAJ+Entrée... des idees sur l'origin du problème?

Фанни
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Эта формула сработала отлично. Спасибо! Однако я хочу применить его к длинному списку значений поиска. Когда я копирую и вставляю формулу, она, очевидно, возвращает те же результаты, что и первое значение поиска, поэтому я удалил $ из $ A $ 11, но это меняет результат на что-то неправильное. Как быстро применить эту же формулу к длинному списку искомых значений?

Спасибо!

M
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, я хочу использовать ключевое слово вместо определенного текста или значения здесь для нескольких возвращаемых значений, не могли бы вы поделиться формулой кода для того же.
Этот комментарий был сведен к минимуму модератором на сайте
Для тех, кто испытывает проблемы в диапазоне больших данных, убедитесь, что у вас нет ячеек с «#N/A», иначе это сломает его. Простая ошибка, но ее можно не заметить.
Этот комментарий был сведен к минимуму модератором на сайте
В этой статье говорится, что используется ВПР, но используется ИНДЕКС... что мне не помогает. Мне нужно решение для нескольких значений с использованием VLOOKUP или XLOOKUP в таблицах.
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL