INDEX і MATCH з кількома масивами

Скажімо, у вас є кілька таблиць із такими самими підписами, як показано нижче, щоб знайти значення, які відповідають критеріям надання з цих таблиць, може бути важкою роботою для вас. У цьому підручнику ми поговоримо про те, як шукати значення в кількох масивах, діапазонах або групах, зіставляючи певні критерії з ІНДЕКС, MATCH та ВИБРАТИ функції.

Як шукати значення в кількох масивах?

Щоб знати, керівники різних груп, що належать до різних відомств, ви можете спочатку скористатися функцією ВИБІР, щоб налаштувати таблицю, з якої повернути ім’я лідера. Функція MATCH потім дізнається позицію лідера в таблиці, до якої він/вона належить. Нарешті, функція INDEX отримає лідера на основі інформації про позицію плюс конкретний стовпець, де перераховані імена лідерів.

Родовий синтаксис

=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)

Щоб знати, лідер групи D, що належить до відділу А, скопіюйте або введіть формулу нижче в клітинку G5 і натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати результат:

=ІНДЕКС(ВИБИР(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),МАТИ (F5,$ B $ 5: $ B $ 8,0),2)

√ Примітка. Знаки долара ($) вище вказують на абсолютні посилання, що означає, що назви та діапазони класів у формулі не зміняться, коли ви переміщуєте чи копіюєте формулу в інші клітинки. Після введення формули перетягніть маркер заповнення вниз, щоб застосувати формулу до наведених нижче клітинок, а потім змініть значення номер_масиву відповідно.

Пояснення формули

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

• CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Функція CHOOSE повертає значення 1st масив із трьох масивів, наведених у формулі. Так воно повернеться $B$5:$C$8, тобто діапазон даних відділу А.
• MATCH(F5;$B$5:$B$8,0;XNUMX): Тип збігу 0 змушує функцію MATCH повертати позицію першого збігу Група D, значення в комірці F5, в масиві $ B $ 5: $ B $ 8, Яка є 4.
• ІНДЕКС (CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5;$B$5:$B$8,0;XNUMX),2) = ІНДЕКС ($B$5:$C$8,4,2): Функція INDEX отримує значення на перетині 4й ряд і 2й стовпець діапазону $B$5:$C$8, Яка є Емілі.

Щоб уникнути змін номер_масиву у формулі щоразу, коли ви її копіюєте, ви можете використовувати допоміжний стовпець, стовпець D. Формула буде виглядати так:

=ІНДЕКС(ВИБИР(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),МАТИ (F5,$ B $ 5: $ B $ 8,0),2)

√ Примітка: цифри 1, 2, 3 у допоміжній колонці вкажіть масив1, масив2, масив3 всередині функції ВИБІР.

Супутні функції

Пов’язані формули