Общая формула
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Резюме
Чтобы найти значение путем сопоставления в нескольких столбцах, вы можете использовать формулу массива, основанную на MMULT, TRANSPOSE, COLUMN и INDEX. В показанном примере формула H4 имеет следующий вид:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
где «имена» - именованный диапазон C4: E7, а «группы» - именованный диапазон B4: B7. Формула возвращает группу, к которой принадлежит каждое имя.
Примечание. Это формула массива, которую необходимо вводить с помощью клавиши Control Shift.
Объяснение
В этой формуле используются следующие логические критерии, действующие изнутри:
--(names=G4)
где names - именованный диапазон C4: E7. Это генерирует результат ИСТИНА / ЛОЖЬ для каждого значения в данных, а двойной отрицательный результат приводит значения ИСТИНА / ЛОЖЬ к 1 и 0, чтобы получить такой массив:
(0,0,0;1,0,0;0,0,0;0,0,0)
Этот массив состоит из 4 строк по 3 столбца, что соответствует структуре «имен».
Второй массив создается с этим выражением:
TRANSPOSE(COLUMN(names)^0))
Функция COLUMN используется для создания числового массива с 3 столбцами и 1 строкой, а TRANSPOSE преобразует этот массив в 1 столбец и 3 строки. Возведение в степень нуля просто преобразует все числа в массиве в 1. Затем функция MMULT используется для умножения матриц:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
и результат передается в функцию ПОИСКПОЗ в виде массива с 1 в качестве значения поиска:
MATCH(1,(0;1;0;0),0)
Функция ПОИСКПОЗ возвращает позицию первого совпадения, которое соответствует строке первой совпадающей строки, соответствующей заданным критериям. Он вводится в ИНДЕКС как номер строки, а именованный диапазон «группы» - как массив:
=INDEX(groups,2)
Наконец, ИНДЕКС возвращает «Медведь», группу, к которой принадлежит Адам.
Литерал содержит критерии
Чтобы проверить конкретные текстовые значения вместо точного совпадения, вы можете использовать функции ЕЧИСЛО и ПОИСК вместе. Например, чтобы сопоставить ячейки, содержащие слово «яблоко», вы можете использовать:
=ISNUMBER(SEARCH("apple",data))
Эта формула объясняется здесь.
