Загальна формула
(=INDEX(rng1,MATCH(MAX(LEN(rng1)*(rng2=criteria)),LEN(rng1)*(rng2=criteria),0)))
Резюме
Щоб знайти найдовший рядок у діапазоні з критеріями, ви можете використовувати формулу масиву на основі INDEX, MATCH, LEN та MAX. У наведеному прикладі формула в F6 має вигляд:
(=INDEX(names,MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)))
Де "імена" - іменований діапазон C5: C14, а "клас" - іменований діапазон B5: B14.
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter.
Пояснення
Ядром цієї формули є функція MATCH, яка визначає позицію найдовшого рядка за допомогою наданих критеріїв:
MATCH(MAX(LEN(names)*(class=F5)),LEN(names)*(class=F5),0)
Примітка MATCH налаштовано для виконання точного збігу, подаючи нуль для типу збігу. Для значення пошуку ми маємо:
LEN(names)*(class=F5)
Функція LEN повертає масив результатів (довжини), по одному для кожного імені у списку, де class = "A" з комірки F5:
(5;6;8;6;6;0;0;0;0;0)
Це ефективно фільтрує весь клас B, і функція MAX потім повертає найбільше значення, 8.
Для побудови масиву підстановки ми використовуємо той самий підхід:
LEN(names)*(class=F5)
І отримати той самий результат:
(5;6;8;6;6;0;0;0;0;0)
Після запуску LEN та MAX ми маємо формулу MATCH з такими значеннями:
MATCH(8,(5;6;8;6;6;0;0;0;0;0),0))
Потім MATCH повертає позицію 8 у списку 3, яка подається до INDEX таким чином:
=INDEX(names,3)
Нарешті, INDEX слушно повертає значення на 3-й позиції імен , тобто "Джонатан".
