Многоразовый ВПР (VLOOKUP)

Имеем список заказов с номерами и названиями товаров. Хотелось бы, для примера, вытаскивать из таблицы по номеру заказа все товары, которые в него входят. Примерно так:

vlookup_all1.gif

 

Замечательная функция ВПР (VLOOKUP) в такой ситуации поможет только частично, т.к. умеет вытаскивать данные только по первому найденному соответствию, т.е. даст нам только Яблоки. Для нахождения и извлечения из таблицы всех наименований лучше использовать формулу массива. Вот такую:

=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Ее надо ввести следующим образом:

  1. выделить ячейки, куда должны выводиться результаты (в нашем примере - это диапазон D6:D20)
  2. ввести (скопировать формулу в первую ячейку) диапазона
  3. нажать Ctrl + Shift + Enter

Вычитание единицы в фрагменте СТРОКА(B2:B16)-1 делается из-за шапки таблицы. По той же причине для компенсации сдвига результирующего диапазона относительно исходного вычитается число пять во фрагменте СТРОКА()-5

Чтобы скрыть ошибку #ЧИСЛО!, которая будет появляться в незаполненных ячейках результирующего диапазона D6:D20 можно использовать функции проверки ошибок ЕСЛИ и ЕОШ, заменив нашу формулу чуть более сложной:

=ЕСЛИ(ЕОШ(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)));"";ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5)))

В Excel 2007 появилась более удобная функция ЕСЛИОШИБКА - она позволяет решить задачу более компактно:

=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5));"")

P.S.

В англоязычной версии Excel эти функции будут выглядеть так:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))),"",INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5)),"")

Ссылки по теме

 



Страницы: 1  2  
28.12.2015 16:03:25
А если к товару приложить еще количество (например в кг) и соответственно возле зеленых ячеек с наименованием товаров, входящих в заказ выводилось еще и соответствующее количество килограммов, тогда как?
11.03.2016 11:18:01
Большое спасибо автору!Очень полезно!
Не подскажите,как и что нужно добавить чтобы поиск был по 2-м переменным?
То есть в данном примере идет поиск по Е2 (№ заказа) ,а что если мне нужно добавить еще одно условие?
Например эта таблица будет иметь еще колонку с датами.Тогда мне нужно искать и по №заказа и по дате.И вывести все,что будет соответствовать этим двум параметрам.Заранее спасибо!
Присоединяюсь ко всем словам благодарности! Полтора дня ломал голову как это сделать, пока один коллега не посоветовал ваш сайт.  Всё получилось с первого раза.  Завтра буду пробовать (как и многие) выводить список по двум параметрам.
Получилось!! Элементарно!..  
Примерно так:   =IF(AND(A2=$P$1;G2=$Q$1);ROW(C2)-1) - это конкретно из моего отчета.
В столбце C (на видео) прописываем двойное условие, и заводим ещё одну ячейку с нужным параметром ( как Е2), к примеру в F2.
Обе эти ячейки я сделал выпадающим списком: при пересечении  двух условий выводится нужный результат.  (Работаю с клиентской базой в отделе продаж).  Условия  в моем случае это маршрут продаж  и день недели, при  "пересечении" которых выпадает список клиентов.

Ещё раз СПАСИБО!  Для меня это большой шаг в собственном развитии!
17.03.2016 14:32:54
Добрый день Николай! А подскажите можно ли данную функцию использовать в проверке данных (как двухуровневый выпадающий список), т.е. в Проверке данных выбрать Список и на основании ячейки Слева (например) в ячейке выпадал только список "Субсчетов" ячейки "Счет".
25.03.2016 12:42:03
Товарищи, а как сделать обратную функцию, кто подскажет? Когда есть список контрагентов, к каждому контрагенту есть подгруппа договоров. Как вывести их в отдельный список, чтобы наименование контрагента было напротив каждого из договоров?
19.04.2016 08:09:25
Всем привет!
Подскажите пож., как можно сделать так, чтобы из этого результата получить выпадающий список, без того чтобы они записывались на лист?
Пробовал эту формулу вставить в "источник списка" он ничего не находит(
27.04.2016 16:43:59
Функция нахождения всех значений, а не только первого, не сработает, если зотя бы в одной ячейке из индексируемых будет присутствовать значение #Н/Д
Добавьте пожалуйста в описание.  
27.10.2016 14:52:11
Добрый день!
Подскажите , пожалуйста, возможно ли применить многоразовый ВПР для нескольких страниц?
Я применяла функцию =ИНДЕКС($L$2:$L$35;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$L$2:$L$35&"'!D4: D200";D3)>0;0))
Данная функция работает, но отбирает только лишь один показатель по критерию отбора , а их допустим три на всех имеющихся листах.  
07.11.2016 10:06:43
Добрый день!

Подскажите пожалуйста формулу в случае если оставить в исходной задаче все без изменения кроме того, что за место цифр в столбце "номер заказа" подставить слова и искать не по точному совпадению, а по наличию слова в ячейке. Например нужно вытащить все номера изделий, напротив, которых в столбце "цвет краски" имеется слово "синий":
Цвет краски №изделия
синий131
светло синий221
темно синий 312
желтый 322
светло желтый417
Желаемый результат работы формулы должен быть таким:
синий131221312
желтый322417
Очень поможете если такая формула есть, иначе руками 27000 наименований прийдется обрабатывать.
26.05.2017 08:48:24
Добрый день, Мирас,
Кажется, у меня есть решение Вашей задачи, если, конечно, решение еще не найдено. :)
09.11.2016 15:05:22
Здравствуйте, Николай! Спасибо за Вашу помощь!
Помогите, пожалуйста, с такими трудностями:
1.Как вывести результаты по заданных критериях в виде списка в одном столбце подряд?
Т.е. если произошла выборка всех результатов согласно первого критерия, то приступить ко второму и выдать результаты согласно второго критерия под результатами первого...
Возможно ли это прописать формулой?
Если исключительно макросом, то подскажите, пожалуйста, где искать и на что опираться.
Очень нужно!
Заранее спасибо!
14.12.2016 12:17:44
Привет. У меня планшет Самсунг, Эксель в приложении OffiseSuite, на клавиатуре нет Shift, а нужны массивные функции.
К тому же я любитель!

Вот что произошло с предлагаемой вами формулой. Задачи одинаковые поменялись лишь аргументы ( номера столбцов и строк,- диапазоны)
Ваша формула
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,""),ROW()-5))
Мой аналог
=INDEX($D$5:$D$14,SMALL(IF($S$2=B6:B15,ROW(D6:D15)-4,""),ROW()-5)) не работает: менял оба смещения подбирал.

=INDEX($D$5:$D$14,SMALL(IF($S$2=B6:B15,ROW(D6:D15)-4,""),1)) с этим изменением работает.
Вопросов много. Нужен диалог
14.12.2016 12:51:02
Теперь бъюсь над ошибками, что забивают пустые ячейки. В арсенале нет =IFERROR, а надо сделать переход по условию "ошибка"
Вот одна подсказка:
"Думаю да. Во всяком случае с ВПР у меня такое работало.
Как-то так попробуйте:
=ЕСЛИ(ЕНД(A1);"Мама";"Рама") "
Пробую
22.01.2017 15:44:42
А если в таблице не две, а несколько колонок? Если это будет работать - напишите, plz, как можно выводить все остальные, заранее спасибо!
Доброго дня! Мне очень нужно , чтобы найденная информация , помещалась не в столбец, а в одну ячейку через запятую . Как это можно реализовать?
И еще мне нужно чтобы информация , собиралась не по одному столбцу, а по 2-3.
Помогите пожалуйста
Добрый день! А как вытащить не "яблоко" или весь список, а только последнее "авокадо"?
22.04.2017 10:00:40
Владимир, поменяйте в формуле НАИМЕНЬШИЙ на НАИБОЛЬШИЙ - и будет вытаскивать в обратном порядке, т.е. начнет сразу с последнего :)
27.06.2017 19:09:10
Спасибо Вам за подробное пояснение! Очень помогло в решении задачки, над которой я ( в силу "начинания" пользования экселем ) долго мучался)
03.08.2017 14:46:54
Здравствуйте,
как улушить мульти ВПР чтоб он искал по выбранным столбцам?

Задача: таблица с названием магазинов в 6 торговых центрах, мне необходимо создать обновляемую таблицу где можно будит из выпадающего списка выбрать название торгового центра и будут автоматически  появяться все магазины которые есть в нем.
RankRetailACMAPABCHEGBEEZEAGH
1Vodafonexxxxxx
2Bijou Brigittexxxxxx
3Douglasxxxxxx
4Nanu-Nanaxxxx
5McPaperxxxxxx
6o2 Germanyxxxxx
7Gamestopxxxxxx
8Deichmannxxxx
9Nordseexxxxx
10DMxxxxx
11Telekom/T-mobilexxxxxx
спасибо
07.09.2017 22:40:55
Спасибо большое.
Если бы мне поставили такую задачу, я не задумываясь бы начал писать макрос и циклом бы проходил массив.
Ваш вариант мне понравился
Спасибо
16.10.2017 00:41:22
Подскажите, а как результат поместить не в массив, а в одну ячейку?
Что бы все товары из заказа отображались в одной ячейке?
Без использования макросов.
27.10.2017 08:01:22
Подскажите пожалуйста команду как повторить то же самое чтобы вводить номер заказа на одном листе, а список "в заказ входят" появлялся на втором, при этом  список товаров находится на третьем
спасибо!
31.10.2017 16:24:12
Добрый день! Замечательная формула! Огромное спасибо! Вопрос: в исходном списке есть повторяющиеся значения, при этом нужно выбрать только последнее из повторяющихся значений, удовлетворяющее условию. Как это можно сделать?
30.01.2018 07:19:57
Чем для такой задачи хуже VLOOKUP2? Я его применяю.
30.01.2018 22:51:50
Здравствуйте! Подскажите пожалуйста, как можно сделать наоборот - есть таблица с данными - в ячейке А1 яблоки, А2 апельсины, А3 виноград, А4 яблоки с указанием количества - в ячейке В1-1, в ячейке В2-2 и тд, нужно найти все встречающиеся яблоки например и вывести их количество, заранее спасибо!
26.04.2018 12:20:07
Добавлю вариант формулы для поиска по двум условиям, долго искал, но нигде не нашел информации.Например, если нам надо искать не только по номеру заказа но и по дате или по какому-то другому критерию:

Исходный вариант :
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Вариант с двумя условиями:
 =ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2=A2:A16;что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))

Николай, если сможете, добавьте к статье, очень часто ищут этот вариант, но его очень непросто найти в интернете.
19.09.2018 16:37:47
Положим вот в таких условиях мы оказались:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2<$A$2:$A$16;F$2>$A$2:$A$16);СТРОКА($B$2:$B$16)-1);СТРОКА()-5))
где Е2=2;F2=5.

Т.е. выбираем товары с номером заказа 3 и 4.

Не работает. Но почему?
27.11.2018 23:36:33
Если не срабатывает вариант с
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(И($E$2=A2:A16;что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
можно попробовать так:
=ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ(($E$2=A2:A16)*(что ищем=где ищем);СТРОКА(B2:B16)-1;"");СТРОКА()-5))
14.01.2019 14:25:56
Спасибо большое! Тоже долго искал решение. Подходит в 2016 только 2 вариант.
26.02.2019 13:42:16
Добрый день.
Возможно ли решить с помощью ВПР следующую задачу:
Имеется таблица по ЗП разбитая по датам: Пример
Нужно просуммировать ЗП каждого сотрудника
В файле итоговое суммирование происходит с помощью функции СУММЕСЛИ, но получается очень громоздко...
Страницы: 1  2  
Наверх