Поиск по нескольким критериям с помощью INDEX и MATCH

Когда вы работаете с большой базой данных в Excel с несколькими столбцами и строками-заголовками, найти запись, соответствующую нескольким условиям, бывает непросто. В таких случаях можно использовать формулу массива с функциями INDEX и MATCH.

Как выполнить поиск по нескольким критериям?

Чтобы найти продукт, который является белым, среднего размера и стоит $18, как показано на изображении выше, вы можете воспользоваться булевой логикой для создания массива из1 и0, чтобы определить строки, соответствующие условиям. Затем функция MATCH определит позицию первой строки, удовлетворяющей всем критериям. После этого INDEX вернет соответствующий идентификатор продукта из этой строки.

Общий синтаксис

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Примечание: Это формула массива, которую необходимо ввести с помощью Ctrl + Shift + Enter.

Чтобы найти продукт, который белый, среднего размера и стоит $18, скопируйте или введите формулу ниже в ячейку H8 и нажмите Ctrl + Shift + Enter для получения результата:

=INDEX(B5:B10,MATCH(1,("White"=C5:C10)*("Medium"=D5:D10)*(18=E5:E10),0))

Или используйте ссылку на ячейку, чтобы сделать формулу динамической:

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

Пояснение к формуле

=INDEX(B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Формула сравнивает цвет в ячейке h3 со всеми цветами в диапазоне C5:C10; сравнивает размер в H5 со всеми размерами в D5:D10; сравнивает цену в H6 со всеми ценами в E5:E10. Начальный результат выглядит так:
  {TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}*{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE}*{FALSE;FALSE;FALSE;TRUE;TRUE;FALSE}.
  Умножение преобразует значения TRUE и FALSE в1 и0:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  После умножения получится единый массив такого вида:
  {0;0;0;0;1;0}.
• MATCH(1,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0),0) = MATCH(1,MATCH(1,{0;0;0;0;1;0},0),0): параметр match_type0 указывает функции MATCH искать точное совпадение. Функция вернет позицию1 в массиве {0;0;0;0;1;0}, то есть 5.
• INDEX(B5:B10B5:B10,MATCH(1,(h3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = INDEX(B5:B10B5:B10,5): функция INDEX возвращает5-е значение из диапазона идентификаторов продукта B5:B10, то есть30005.

Связанные функции

Связанные формулы