Общая формула
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Резюме
Чтобы найти значения с помощью INDEX и MATCH, используя несколько критериев, вы можете использовать формулу массива. В показанном примере формула H8 имеет следующий вид:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Примечание: это формула массива, и ее нужно вводить с помощью Ctrl + Shift + Enter, кроме Excel 365.
Объяснение
Это более продвинутая формула. Для получения информации см. Как использовать ИНДЕКС и ПОИСКПОЗ.
Обычно формула ИНДЕКС ПОИСКПОЗ сконфигурирована с ПОИСКПОЗ, настроенным для просмотра диапазона из одного столбца и обеспечения соответствия на основе заданных критериев. Без объединения значений во вспомогательном столбце или в самой формуле невозможно предоставить более одного критерия.
Эта формула позволяет обойти это ограничение, используя логическую логику для создания массива единиц и нулей для представления строк, соответствующих всем 3 критериям, а затем с помощью MATCH для сопоставления первой найденной 1. Временный массив единиц и нулей создается с помощью этого фрагмента:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Здесь мы сравниваем элемент в H5 со всеми элементами, размер в H6 со всеми размерами и цвет в H7 со всеми цветами. Первоначальный результат - это три массива результатов ИСТИНА / ЛОЖЬ, например:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Совет: используйте F9, чтобы увидеть эти результаты. Просто выберите выражение в строке формул и нажмите F9.
Математическая операция (умножение) преобразует значения ИСТИНА ЛОЖЬ в единицы и нули:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
После умножения у нас есть такой единственный массив:
(0;0;1;0;0;0;0)
который передается в функцию MATCH как поисковый массив со значением поиска 1:
MATCH(1,(0;0;1;0;0;0;0))
На данный момент формула является стандартной формулой ИНДЕКС ПОИСКПОЗ. Функция ПОИСКПОЗ возвращает 3 в ИНДЕКС:
=INDEX(E5:E11,3)
а ИНДЕКС возвращает окончательный результат 17,00 долларов США.
Визуализация массива
Массивы, описанные выше, может быть трудно визуализировать. На изображении ниже показана основная идея. Столбцы B, C и D соответствуют данным в примере. Столбец F создается путем умножения трех столбцов вместе. Это массив, переданный в MATCH.
Версия без массива
К этой формуле можно добавить еще один ИНДЕКС, избегая необходимости вводить формулу массива с помощью Ctrl + Shift + Enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Функция ИНДЕКС может обрабатывать массивы изначально, поэтому второй ИНДЕКС добавляется только для того, чтобы «поймать» массив, созданный с помощью логической логической операции, и снова вернуть тот же массив в ПОИСКПОЗ. Для этого INDEX настроен с нулевыми строками и одним столбцом. Уловка с нулевой строкой заставляет INDEX возвращать столбец 1 из массива (который в любом случае уже является одним столбцом).
Зачем вам нужна версия без массива? Иногда люди забывают ввести формулу массива с помощью Ctrl + Shift + Enter, и формула возвращает неверный результат. Таким образом, формула без массива более «пуленепробиваемая». Однако это более сложная формула.
Примечание: В Excel 365 нет необходимости вводить формулы массива специальным образом.
