Как найти значение с помощью ВПР по нескольким критериям в Excel?
В Excel с помощью функции ВПР можно быстро найти нужные данные в списке по одному заданному критерию. Но как быть, если требуется найти значение по нескольким критериям, как показано на скриншоте ниже?
Поиск значения с помощью ВПР по нескольким критериям с использованием функции ПРОСМОТР
Поиск значения с помощью ВПР по нескольким критериям с использованием функций ИНДЕКС и ПОИСКПОЗ
Поиск значения с помощью ВПР по нескольким критериям с помощью удобной функции
Допустим, у меня есть следующий диапазон данных, по которому я хочу использовать два критерия для получения соответствующего значения. Например, я знаю товар и его цвет и хочу найти имя продавца из той же строки:
Поиск значения с помощью ВПР по нескольким критериям с использованием функции ПРОСМОТР
Функция ПРОСМОТР поможет вам решить эту задачу. Введите формулу в нужную ячейку и нажмите клавишу Enter, чтобы получить желаемый результат (см. скриншот):
Примечание: В приведённой выше формуле:
- A2:A12=G2: означает поиск критерия из ячейки G2 в диапазоне A2:A12;
- C2:C12=H2: означает поиск критерия из ячейки H2 в диапазоне C2:C12;
- E2:E12: указывает диапазон, из которого нужно вернуть соответствующее значение.
Совет: Если у вас больше двух критериев, просто добавьте их в формулу следующим образом:
=LOOKUP(2,1/($A$2:$A$12=G2)/($B$2:$B$12=H2)/($C$2:$C$12=I2),($E$2:$E$12)).
Поиск значения с помощью ВПР по нескольким критериям с использованием функций ИНДЕКС и ПОИСКПОЗ
В Excel комбинация функций ИНДЕКС и ПОИСКПОЗ — это мощный инструмент для поиска значений по одному или нескольким критериям. Чтобы разобраться, как работает эта формула, выполните следующие действия:
Введите приведённую ниже формулу в пустую ячейку и нажмите одновременно клавиши Ctrl + Shift + Enter — после этого вы получите нужное значение (см. скриншот):
Примечание: В приведённой выше формуле:
- A2:A12=G2: означает поиск критерия из ячейки G2 в диапазоне A2:A12;
- C2:C12=H2: означает поиск критерия из ячейки H2 в диапазоне C2:C12;
- E2:E12: обозначает диапазон, из которого необходимо вернуть соответствующее значение.
Совет: Если у вас больше двух критериев, просто добавьте их в формулу следующим образом: =INDEX($E$2:$E$12,MATCH(1,($A$2:$A$12=G2)*($B$2:$B$12=H2)*($C$2:$C$12=I2),0)).
Поиск значения с помощью ВПР по нескольким критериям с помощью удобной функции
Если у вас установлен Kutools для Excel, вы сможете быстро получить нужные значения по нескольким критериям с помощью его функции Поиск — Многокритериальный поиск.
После установки Kutools для Excelвыполните следующие действия:
1. Нажмите Kutools > Супер ПОИСК > Поиск по нескольким условиям (см. скриншот).
2. В диалоговом окне Поиск — Многокритериальный поиск выполните следующие действия:
- (1.) В разделе Диапазон значений поискапоследовательно укажите диапазон искомых значений или выберите столбец искомых значений, удерживая клавишу Ctrl, на основе которых нужно выполнять поиск значений с помощью ВПР;
- (2.) В разделе Область размещения спискавыберите Область размещения списка, куда следует поместить результаты поиска;
- (3.) В разделе Ключевой столбецпоследовательно выберите соответствующие Ключевой столбец, содержащие Диапазон значений поиска, удерживая клавишу Ctrl;
- Примечание: количество столбцов, выбранных в поле Ключевой столбец, должно совпадать с количеством столбцов, выбранных в поле Диапазон значений поиска, а порядок каждого выбранного столбца в поле Ключевой столбец должен строго соответствовать порядку столбцов с критериями в поле Диапазон значений поиска.
- (4.) В разделе Столбец для возврата выберите столбец, содержащий нужное вам возвращаемое значение.
3. Затем нажмите кнопку OK или Применить — и все найденные значения, соответствующие нескольким критериям, будут извлечены сразу (см. скриншот).
Другие связанные статьи:
- Поиск значений с помощью ВПР по нескольким листам
- В Excel с помощью функции ВПР легко получить соответствующие значения из одной таблицы на листе. Но задумывались ли вы, как выполнить поиск по нескольким листам одновременно с помощью ВПР? Допустим, у вас есть три листа с диапазонами данных, и вы хотите получить нужные значения на основе определённых критериев из этих трёх листов. Как решить такую задачу в Excel?
- ВПР: возвращать пустое или определённое значение вместо 0 или #Н/Д в Excel
- При использовании функции ВПР, если ячейка с совпадением пуста, функция возвращает 0, а если искомое значение не найдено — ошибку #Н/Д, как показано на скриншоте ниже. Как настроить формулу так, чтобы вместо 0 или ошибки #Н/Д отображалась пустая ячейка или заданный вами текст?
- ВПР и возврат соответствующих данных между двумя значениями
- В Excel стандартная функция ВПР позволяет получать соответствующие значения на основе заданных данных. Однако иногда возникает задача — выполнить поиск с помощью ВПР и вернуть значение, расположенное между двумя другими значениями, как показано на следующем скриншоте. Как решить такую задачу в Excel?
- ВПР и возврат всей строки / Вся строка найденного значения
- Обычно с помощью функции ВПР можно найти и вернуть одно совпадение из диапазона данных, но пробовали ли вы находить и возвращать всю строку данных на основе заданных критериев, как показано на следующем скриншоте?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек