Поиск по нескольким критериям с помощью ИНДЕКС и ПОИСКПОЗ

При работе с большой базой данных в электронной таблице Excel, содержащей множество столбцов и строк с заголовками, бывает сложно найти записи, соответствующие сразу нескольким критериям. В этом случае на помощь приходит формула массива с функциями ИНДЕКС и ПОИСКПОЗ.

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

Чтобы найти продукт, который является белым и средним по размеру и стоит $18, как показано на рисунке выше, используйте булеву логику для создания массива из единиц и нулей, указывающего строки, соответствующие всем критериям. Затем функция ПОИСКПОЗ определит позицию первой подходящей строки, а функция ИНДЕКС — соответствующий идентификатор продукта в этой строке.

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

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

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

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

=ИНДЕКС()B5:B10,ПОИСКПОЗ(1,())«Белый»=C5:C10)*(«Средний»=D5:D10)*(18=E5:E10);0))

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

=ИНДЕКС()B5:B10;ПОИСКПОЗ(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. Первоначальный результат выглядит следующим образом:
  {ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ИСТИНА;ЛОЖЬ}*{ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ}*{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ}.
  Умножение преобразует значения ИСТИНА и ЛОЖЬ в 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}.
• ПОИСКПОЗ(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)=ПОИСКПОЗ(1,)Аргумент тип_сопоставления, равный 0, указывает функции ПОИСКПОЗ найти точное совпадение. Затем функция возвращает позицию 1 в массиве {0;0;0;0;1;0}, которая равна 5.
• ИНДЕКС()B5:B10,ПОИСКПОЗ(1,)(H3=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0)) = ИНДЕКС(B5:B10Функция ИНДЕКС возвращает 5-е значение из диапазона идентификаторов продуктов B5:B10, то есть 30005.

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

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