INDEX і MATCH з кількома масивами
Скажімо, у вас є кілька таблиць із такими самими підписами, як показано нижче, щоб знайти значення, які відповідають критеріям надання з цих таблиць, може бути важкою роботою для вас. У цьому підручнику ми поговоримо про те, як шукати значення в кількох масивах, діапазонах або групах, зіставляючи певні критерії з ІНДЕКС, MATCH та ВИБРАТИ функції.
Як шукати значення в кількох масивах?
Щоб знати, керівники різних груп, що належать до різних відомств, ви можете спочатку скористатися функцією ВИБІР, щоб налаштувати таблицю, з якої повернути ім’я лідера. Функція MATCH потім дізнається позицію лідера в таблиці, до якої він/вона належить. Нарешті, функція INDEX отримає лідера на основі інформації про позицію плюс конкретний стовпець, де перераховані імена лідерів.
Родовий синтаксис
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- номер_масиву: Число CHOOSE, що використовується для позначення масиву зі списку масив1, масив2,… щоб повернути результат з.
- масив1, масив2,…: Масиви для повернення результату. Тут йдеться про три таблиці.
- пошукове_значення: Значення, використане формулою комбінації для визначення позиції відповідного лідера. Тут відноситься до даної групи.
- пошуковий_масив: Діапазон клітинок, де lookup_value перераховано. Тут йдеться про груповий діапазон. Примітка. Ви можете використовувати діапазон групи з будь-якого відділу, оскільки всі вони однакові, і нам просто потрібно отримати номер позиції.
- номер_столбця: Стовпець, з якого ви хочете отримати дані.
Щоб знати, лідер групи 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 всередині функції ВИБІР.
Супутні функції
Функція Excel INDEX повертає відображене значення на основі заданої позиції з діапазону або масиву.
Функція Excel MATCH здійснює пошук певного значення в діапазоні комірок і повертає відносне положення значення.
Функція CHOOSE повертає значення зі списку аргументів значення на основі заданого номера індексу. Наприклад, CHOOSE(3,”Apple”,”Peach”,”Orange”) повертає помаранчевий, номер індексу дорівнює 3, а оранжевий – це третє значення після номера індексу у функції.
Пов’язані формули
Шукати значення з іншого аркуша чи книги
Якщо ви знаєте, як використовувати функцію VLOOKUP для пошуку значень на аркуші, значення vlookup з іншого аркуша чи книги не становлять для вас проблем.
Vlookup з динамічною назвою аркуша
У багатьох випадках вам може знадобитися зібрати дані на декількох робочих аркушах для короткого викладу. За допомогою комбінації функції VLOOKUP та функції INDIRECT ви можете створити формулу для пошуку конкретних значень у робочих аркушах з динамічним іменем аркуша.
Пошук за кількома критеріями за допомогою INDEX та MATCH
Коли ви маєте справу з великою базою даних у електронній таблиці Excel з кількома стовпцями та підписами рядків, завжди складно знайти щось, що відповідає кільком критеріям. У цьому випадку можна використовувати формулу масиву з функціями INDEX і MATCH.
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel - допомагає виділитися з натовпу
Kutools для Excel має понад 300 функцій, Переконайтеся, що те, що вам потрібно, знаходиться лише на відстані одного кліка...
Вкладка Office - увімкніть читання та редагування вкладок у Microsoft Office (включаючи Excel)
- Одна секунда для перемикання між десятками відкритих документів!
- Щодня зменшуйте сотні клацань мишею, прощайте руку миші.
- Збільшує вашу продуктивність на 50% під час перегляду та редагування декількох документів.
- Додає ефективні вкладки в Office (включно з Excel), як у Chrome, Edge та Firefox.
