Резюме
Щоб витягти кілька збігів в окремі комірки, в окремих стовпцях можна використовувати формулу масиву на основі INDEX та SMALL. У наведеному прикладі формула в F5 має вигляд:
(=IFERROR(INDEX(names,SMALL(IF(groups=$E5,ROW(names)-MIN(ROW(names))+1),COLUMNS($E$5:E5))),""))
Це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Після введення формули в першу клітинку перетягніть її вниз і впоперек, щоб заповнити інші комірки.
Пояснення
Примітка: ця формула використовує два іменовані діапазони: "імена" стосуються C5: C11, а "групи" стосуються B5: B11. Ці імена також визначені на знімку екрана вище.
Суть цієї формули така: ми використовуємо функцію SMALL для створення номера рядка, що відповідає "n-му збігу". Отримавши номер рядка, ми просто передаємо його у функцію функції INDEX, яка повертає значення в цьому рядку.
Фокус у тому, що SMALL працює з масивом, який динамічно будується IF у цьому біті:
IF(groups=$E5,ROW(names)-MIN(ROW(names))+1)
Цей фрагмент перевіряє названі "групи" діапазону на значення в E5. Якщо знайдено, він повертає номер рядка з масиву відносних номерів рядків, створених за допомогою:
ROW(names)-MIN(ROW(names))+1
Кінцевим результатом є масив, що містить числа, де є збіг, і FALSE, де ні:
(1; FALSE; FALSE; FALSE; FALSE; 6; FALSE)
Цей масив переходить у SMALL. Значення k для SMALL (n-го) походить із розширеного діапазону:
COLUMNS($E$5:E5)
При копіюванні в таблиці результатів діапазон розширюється, приводячи до збільшення k (n-го). Функція SMALL повертає кожен відповідний номер рядка, який надається функції INDEX як номер_ рядка, а масив має іменований діапазон "names".
Помилки обробки
Коли COLUMNS повертає значення k, яке не існує, SMALL видає помилку #NUM. Це відбувається після того, як відбулися всі збіги. Щоб придушити помилку, ми обертаємо формулу у функцію IFERROR, щоб виявити помилки та повернути порожній рядок ("").
