Загальна формула
(=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. Формула повертає групу, якій належить кожне ім'я.
Примітка: це формула масиву, і її потрібно вводити за допомогою enter control shift.
Пояснення
Працюючи зсередини, логічними критеріями, які використовуються у цій формулі, є:
--(names=G4)
де names - іменований діапазон C4: E7. Це генерує результат TRUE / FALSE для кожного значення в даних, а подвійний негатив примушує значення TRUE FALSE до 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))
і отриманий результат переходить у функцію MATCH як масив, а 1 - як значення пошуку:
MATCH(1,(0;1;0;0),0)
Функція MATCH повертає позицію першої відповідності, яка відповідає рядку першого відповідного рядка, що відповідає заданим критеріям. Це подається в INDEX як номер рядка, а як масив називається діапазон "groups":
=INDEX(groups,2)
Нарешті, INDEX повертає "Ведмідь", до якої належить Адам.
Буквене значення містить критерії
Щоб перевірити конкретні текстові значення замість точної відповідності, ви можете використовувати функції ISNUMBER та SEARCH разом. Наприклад, для відповідності комірок, що містять "яблуко", ви можете використовувати:
=ISNUMBER(SEARCH("apple",data))
Ця формула пояснюється тут.
