Поиск по нескольким условиям с INDEX-MATCH

Иногда один столбец не позволяет однозначно определить строку. Например, может потребоваться цена товара определённого размера или зарплата сотрудника конкретного отдела.

В таком случае нужен поиск по нескольким условиям: сопоставление по двум или более столбцам одновременно, чтобы точно определить одну строку.

INDEX-MATCH элегантно решает эту задачу, объединяя условия в одну проверку совпадения без дополнительных вспомогательных столбцов.

Проще всего представить это так: объедините ключевые столбцы в один. Добавьте вспомогательный столбец, соединяющий товар и размер, а затем выполните обычный поиск по нему.

Например, вспомогательная ячейка может содержать =A2&"|"&B2, что даст значение "Shirt|Large". Затем выполните MATCH для поиска "Shirt|Large" в объединённом столбце.

Это работает, но загромождает таблицу. В следующих разделах показано, как полностью отказаться от вспомогательного столбца.

Основной приём: перемножьте проверки двух условий внутри MATCH.

(A2:A10=G1) создаёт массив TRUE/FALSE для первого условия. (B2:B10=G2) делает то же самое для второго. Их перемножение, (A2:A10=G1)*(B2:B10=G2), даёт 1 только там, где оба условия равны TRUE, и 0 в остальных случаях.

Затем MATCH ищет значение 1, чтобы найти строку, соответствующую обоим условиям.

В электронных таблицах TRUE ведёт себя как 1, а FALSE — как 0. Перемножение этих значений имитирует логическое AND:

• 1 умножить на 1 = 1 (оба условия выполнены)
• 1 умножить на 0 = 0
• 0 умножить на 1 = 0
• 0 умножить на 0 = 0

Поэтому только строки, в которых выполнены оба условия, дают 1. Во всех остальных строках получается 0. Эта единственная 1 отмечает нужную строку.

Теперь оберните перемноженный массив в MATCH и выполните поиск точного значения 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) возвращает позицию первой строки, в которой оба условия равны TRUE.

Если подходящее сочетание находится в четвёртой строке данных, MATCH возвращает 4. Именно эта позиция нужна INDEX для получения результата.

Передайте результат MATCH в INDEX, указав столбец, из которого нужно получить значение, например цены в C2:C10.

Полная формула означает: в диапазоне C2:C10 вернуть значение строки, где товар равен G1 и размер равен G2.

Это полноценный поиск по нескольким условиям без вспомогательного столбца и изменения порядка данных.

Эта формула обрабатывает массивы условий. В современном Excel и Google Sheets достаточно нажать Enter — формула будет работать.

В старом Excel (до появления динамических массивов) необходимо подтвердить формулу массива сочетанием Ctrl+Shift+Enter, после чего появятся фигурные скобки. Если в устаревшей версии Excel результат неправильный или отображается ошибка, обычно отсутствует именно этот шаг подтверждения.

Нужно использовать три условия? Просто добавьте ещё одну проверку при умножении. Предположим, Вы также хотите сопоставить цвет в столбце D со значением из G3.

Каждый дополнительный множитель (range=criterion) ещё сильнее сужает результат. Только строки, в которых все условия равны TRUE, сохраняют произведение 1; любое FALSE превращает всё произведение в 0.

Этот принцип можно расширять на любое необходимое количество столбцов.

Данные: A = товар, B = размер, C = цена. Нужно найти цену "Shirt" размера "Large".

• G1 = "Shirt", G2 = "Large".
• Массивы условий дают 1 только в строке Shirt+Large, например в строке 4.
• MATCH(1, ..., 0) возвращает 4.
• INDEX(C2:C10, 4) возвращает цену из этой строки.

Измените любое входное значение — и формула мгновенно найдёт нужную строку заново.

Помните о следующем:

• Одинаковые диапазоны: каждый диапазон условий и столбец INDEX должны иметь одинаковую высоту.
• Отсутствие совпадений: если ни одна строка не соответствует всем условиям, MATCH возвращает #N/A. Оберните всю формулу в IFERROR.
• Дубликаты: если подходит несколько строк, MATCH возвращает только первую. Сделайте условия достаточно точными, чтобы результат был уникальным.

Если совпадения могут находиться в нескольких строках и вместо извлечения одного значения необходимо суммировать их значения, SUMPRODUCT станет удобной альтернативой INDEX-MATCH с вводом формулы массива.

Эта функция умножает массивы условий на столбец значений и складывает результаты, поэтому вклад в сумму вносят только строки, соответствующие обоим критериям. Нажимать Ctrl+Shift+Enter не требуется, поскольку SUMPRODUCT изначально работает с массивами.

Используйте INDEX-MATCH для извлечения одного подходящего значения, а SUMPRODUCT — для суммирования значений всех совпадений.

Проверьте свои знания поиска по нескольким критериям.

Для поиска по нескольким критериям с помощью INDEX-MATCH:

• Перемножьте массивы условий: (A=G1)*(B=G2) даёт 1 только там, где выполняются все условия (логическое AND).
• MATCH(1, ..., 0) находит позицию этой строки.
• INDEX(returnCol, position) возвращает значение.

Для добавления условий используйте дополнительные множители *(range=criterion), следите, чтобы диапазоны имели одинаковую высоту, в старых версиях Excel подтверждайте формулу нажатием Ctrl+Shift+Enter и обрабатывайте ошибки с помощью IFERROR.