Комбіновані функції INDEX & MATCH у Excel (10 простих прикладів)

Excel має багато функцій - близько 450+ з них.

І багато з них просто чудові. Обсяг роботи, яку ви можете виконати за кількома формулами, все ще дивує мене (навіть після використання Excel понад 10 років).

І серед усіх цих чудових функцій виділяється комбінація функцій INDEX MATCH.

Я великий шанувальник комбінації INDEX MATCH, і я багато разів давав це зрозуміти.

Я навіть написав статтю про Index Match VS VLOOKUP, яка викликала невеликі дебати (ви можете перевірити розділ коментарів на наявність феєрверків).

І сьогодні я пишу цю статтю, присвячену виключно збігу індексів, щоб показати вам кілька простих та просунутих сценаріїв, де ви можете використовувати цю потужну комбінацію формул і виконати роботу.

Примітка: У Excel є інші формули пошуку, такі як VLOOKUP та HLOOKUP, і вони чудові. Багато людей вважають VLOOKUP простішим у використанні (і це також правда). Я вважаю, що INDEX MATCH є кращим варіантом у багатьох випадках. Але оскільки людям важко, воно менше вживається. Тому я намагаюся спростити це за допомогою цього підручника.

Тепер, перш ніж я покажу вам, як поєднання INDEX MATCH змінює світ аналітиків та дослідників даних, дозвольте мені спершу познайомити вас з окремими частинами - функціями INDEX та MATCH.

Функція ІНДЕКС: знаходить значення на основі координат

Найпростіший спосіб зрозуміти, як працює функція індексу, - уявити її як супутник GPS.

Як тільки ви скажете супутнику координати широти та довготи, він точно знатиме, куди йти і знайти це місце.

Тож, незважаючи на вражаючу кількість комбінацій довгих ширин, супутник точно знатиме, де шукати.

Я швидко пошукав своє робоче місце, і ось що я отримав.

У всякому разі, досить географії.

Так само, як супутнику потрібні координати широти та довготи, функції INDEX у Excel знадобиться номер рядка та стовпця, щоб знати, на яку клітинку ви посилаєтесь.

І це функція Excel INDEX у горіховій оболонці.

Тож дозвольте мені визначити це простими словами для вас.

Функція INDEX буде використовувати номер рядка та номер стовпця, щоб знайти клітинку в заданому діапазоні та повернути значення в ній.

Сам по собі INDEX - це дуже проста функція, без утиліти. Адже в більшості випадків вам навряд чи будуть відомі номери рядків і стовпців.

Але…

Той факт, що ви можете використовувати його з іншими функціями (підказка: MATCH), які можуть знайти номер рядка та номер стовпця, робить INDEX надзвичайно потужною функцією Excel.

Нижче наведено синтаксис функції INDEX:

= ІНДЕКС (масив, номер ряду, [номер_кол.]) = ІНДЕКС (масив, номер ряду, [номер_кол.], [Номер_області])

• масив - а діапазон комірок або константа масиву.
• рядок_число - номер рядка, з якого потрібно отримати значення.
• [col_num] - номер стовпця, з якого потрібно отримати значення. Хоча це необов’язковий аргумент, але якщо номер_строки не вказано, його потрібно надати.
• [номер_площі] - (Необов’язково) Якщо аргумент масиву складається з декількох діапазонів, це число буде використовуватися для вибору посилання з усіх діапазонів.

Функція INDEX має 2 синтаксису (лише для ознайомлення).

У більшості випадків використовується перший. Другий використовується лише в просунутих випадках (наприклад, тривимірний пошук), про який ми розповімо в одному з прикладів далі в цьому підручнику.

Але якщо ви новачок у цій функції, просто запам’ятайте перший синтаксис.

Нижче наведено відео, яке пояснює, як користуватися функцією INDEX

Функція MATCH: знаходить позицію на основі значення пошуку

Повертаючись до мого попереднього прикладу довготи та широти, MATCH - це функція, яка може знайти ці позиції (у світі електронних таблиць Excel).

Простими мовами, функція Excel MATCH може знайти положення клітинки в діапазоні.

І на якій підставі вона знайде позицію клітини?

На основі значення пошуку.

Наприклад, якщо у вас є список, як показано нижче, і ви хочете знайти в ньому позицію імені "Позначити", то ви можете скористатися функцією MATCH.

Функція повертає 3, оскільки це позиція комірки з назвою Позначити в ній.

Функція MATCH починає шукати зверху вниз значення пошуку (яке є «Позначкою») у зазначеному діапазоні (це A1: A9 у цьому прикладі). Як тільки він знаходить назву, він повертає позицію в цьому конкретному діапазоні.

Нижче наведено синтаксис функції MATCH в Excel.

= MATCH (lookup_value, lookup_array, [match_type])

• lookup_value - Значення, для якого ви шукаєте відповідність у lookup_array.
• lookup_array - Діапазон клітинок, у якому ви шукаєте lookup_value.
• [match_type] - (Необов’язково) Це вказує, як Excel повинен шукати відповідне значення. Він може приймати три значення -1, 0 або 1.

Розуміння аргументу типу відповідності у функції MATCH

Про функцію MATCH вам потрібно знати ще одну річ - про те, як вона проходить через дані та знаходить положення комірки.

Третій аргумент функції MATCH може бути 0, 1 або -1.

Нижче наведено пояснення того, як працюють ці аргументи:

• 0 - це буде шукати точну відповідність значення. Якщо буде знайдено точне збіг, функція MATCH поверне позицію комірки. В іншому випадку він поверне помилку.
• 1 - це знаходить найбільше значення, яке менше або дорівнює значенню пошуку. Щоб це спрацювало, ваш діапазон даних потрібно відсортувати за зростанням.
• -1 - це знаходить найменше значення, яке більше або дорівнює значенню пошуку. Щоб це спрацювало, ваш діапазон даних потрібно відсортувати в порядку зменшення.

Нижче наведено відео, яке пояснює, як використовувати функцію MATCH (разом з аргументом типу відповідності)

Узагальнюючи і висловлюючи це простими словами:

• INDEX потребує позиції комірки (номер рядка та стовпця) і дає значення комірки.
• MATCH знаходить позицію за допомогою значення пошуку.

Давайте об’єднаємо їх, щоб створити електростанцію (INDEX + MATCH)

Тепер, коли у вас є базове уявлення про те, як функції INDEX та MATCH працюють окремо, давайте об’єднаємо ці дві та дізнаємось про всі чудові речі, які вона може зробити.

Щоб краще зрозуміти це, у мене є кілька прикладів, які використовують комбінацію INDEX MATCH.

Я почну з простого прикладу, а потім також покажу вам деякі розширені варіанти використання.

Натисніть тут, щоб завантажити файл прикладу

Приклад 1: Простий пошук за допомогою комбінації INDEX MATCH

Давайте зробимо простий пошук за допомогою INDEX/MATCH.

Нижче наведено таблицю, де я маю оцінки для десяти учнів.

З цієї таблиці я хочу знайти позначки для Джима.

Нижче наведена формула, за допомогою якої можна легко це зробити:

= ІНДЕКС ($ A $ 2: $ B $ 11, MATCH ("Джим", $ A $ 2: $ A $ 11,0), 2)

Тепер, якщо ви думаєте, що це легко можна зробити за допомогою функції VLOOKUP, ви праві! Це не найкраще використання чудового значення INDEX MATCH. Незважаючи на те, що я прихильник INDEX MATCH, це трохи складніше, ніж VLOOKUP. Якщо ви хочете лише отримати дані зі стовпця праворуч, я рекомендую вам скористатися VLOOKUP.

Причина, чому я показав цей приклад, який також можна легко зробити за допомогою VLOOKUP, полягає в тому, щоб показати вам, як працює INDEX MATCH у простих налаштуваннях.

Тепер дозвольте мені показати переваги INDEX MATCH.

Припустимо, у вас є ті самі дані, але замість того, щоб мати їх у стовпцях, ви маєте їх у рядках (як показано нижче).

Знаєте що, ви все ще можете використовувати комбінацію INDEX MATCH, щоб отримати оцінки Джима.

Нижче наведена формула, яка дасть вам результат:

= ІНДЕКС ($ B $ 1: $ K $ 2,2, MATCH ("Джим", $ B $ 1: $ K $ 1,0))

Зауважте, що вам потрібно змінити діапазон і змінити частини рядка/стовпця, щоб ця формула працювала і для горизонтальних даних.

Це неможливо зробити за допомогою VLOOKUP, але ви все одно можете це легко зробити за допомогою HLOOKUP.

Комбінація INDEX MATCH може легко обробляти горизонтальні та вертикальні дані.

Натисніть тут, щоб завантажити файл прикладу

Приклад 2: Пошук ліворуч

Це частіше, ніж ви думаєте.

Багато разів вам може знадобитися отримати дані зі стовпця ліворуч від стовпця, що має значення пошуку.

Щось, як показано нижче:

Щоб дізнатися про продажі Майкла, вам потрібно буде шукати зліва.

Якщо ви думаєте про VLOOKUP, дозвольте мені зупинитись тут.

VLOOKUP не призначений для пошуку та вилучення значень зліва.

Чи можете ви все ще зробити це за допомогою VLOOKUP?

Так, ти можеш!

Але це може перетворитися на довгу і потворну формулу.

Тому, якщо ви хочете здійснити пошук та отримати дані зі стовпців ліворуч, краще скористайтеся комбінацією INDEX MATCH.

Нижче наведена формула, за якою буде отримано номер продажів Майкла:

= ІНДЕКС ($ A $ 2: $ C $ 11, MATCH ("Майкл", C2: C11,0), 2)

Ще один момент тут щодо ІНДЕКСНОГО МАТЧУ. VLOOKUP може отримувати дані лише зі стовпців, розташованих праворуч від стовпця, що має значення пошуку.

Приклад 3: Двосторонній пошук

Досі ми бачили приклади, коли ми хотіли отримати дані зі стовпця, що прилягає до стовпця, що має значення пошуку.

Але в реальному житті дані часто охоплюють кілька стовпців.

INDEX MATCH може легко обробляти двосторонній пошук.

Нижче наводиться набір оцінок студента з трьох різних предметів.

Якщо ви хочете швидко отримати оцінки студента з усіх трьох предметів, ви можете зробити це за допомогою INDEX MATCH.

Наведена нижче формула дасть вам оцінки для Джима для всіх трьох предметів (скопіюйте та вставте в одну клітинку та перетягніть, щоб заповнити інші клітини або скопіюйте та вставте в інші клітини).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

Дозвольте мені також швидко пояснити цю формулу.

Формула INDEX використовує B2: D11 як діапазон.

Перший MATCH використовує ім’я (Джим у комірці F3) і отримує його положення у стовпці імен (A2: A11). Це стає номером рядка, з якого потрібно отримати дані.

Друга формула MATCH використовує ім’я суб’єкта (у комірці G2), щоб отримати позицію цього конкретного імені суб’єкта у B1: D1. Наприклад, математика - 1, фізика - 2, хімія - 3.

Оскільки ці позиції MATCH подаються до функції INDEX, вона повертає оцінку на основі імені студента та назви предмета.

Ця формула є динамічною, а це означає, що якщо ви зміните ім’я учня або назви предметів, він все одно працюватиме і отримуватиме правильні дані.

Однією чудовою рисою використання INDEX/MATCH є те, що навіть якщо ви поміняєте імена предметів, вони продовжать давати правильний результат.

Приклад 4: Пошук значення з кількох стовпців/критеріїв

Припустимо, у вас є набір даних, як показано нижче, і ви хочете отримати позначки для "Позначити довго".

Оскільки дані містяться у двох стовпцях, я не можу шукати Марка та отримувати дані.

Якщо я так зроблю, я збираюся отримати дані про оцінки для Марка Фроста, а не Марка Лонга (оскільки функція MATCH дасть мені результат для МАРКУ, якій вона відповідає).

Один із способів зробити це - створити допоміжний стовпець та об’єднати імена. Після того, як у вас є допоміжний стовпець, ви можете скористатися VLOOKUP і отримати дані про позначки.

Якщо вам цікаво дізнатися, як це зробити, прочитайте цей підручник з використання VLOOKUP з кількома критеріями.

Але за допомогою комбінації INDEX/MATCH вам не потрібен допоміжний стовпець. Ви можете створити формулу, яка обробляє кілька критеріїв у самій формулі.

Наведена нижче формула дасть результат.

= ІНДЕКС ($ C $ 2: $ C $ 11, МАТЧ ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

Дозвольте мені швидко пояснити, що робить ця формула.

Частина формули MATCH поєднує значення пошуку (Позначити і Довго), а також весь масив пошуку. Коли $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 використовується як масив пошуку, він фактично перевіряє значення пошуку порівняно з об’єднаним рядком імені та прізвища (розділений символом конвеєра).

Це гарантує, що ви отримаєте правильний результат, не використовуючи жодних допоміжних стовпців.

Ви також можете здійснити такий вид пошуку (де є кілька стовпців/критеріїв) за допомогою VLOOKUP, але вам потрібно використовувати допоміжний стовпець. Комбінація INDEX MATCH дозволяє трохи легше це зробити без допоміжних стовпців.

Приклад 5: Отримати значення з усього рядка/стовпця

У наведених вище прикладах ми використовували функцію INDEX для отримання значення з певної комірки. Ви вводите номер рядка та стовпця, і він повертає значення у цій конкретній комірці.

Але ви можете зробити більше.

Ви також можете скористатися функцією INDEX, щоб отримати значення з цілого рядка або стовпця.

Ви запитаєте, наскільки це може бути корисним!

Припустимо, ви хочете дізнатися загальний бал Джима з усіх трьох предметів.

Ви можете скористатися функцією INDEX, щоб спочатку отримати всі оцінки Джима, а потім скористатися функцією SUM, щоб отримати загальну суму.

Давайте подивимось, як це зробити.

Нижче я наводжу бали всіх учнів з трьох предметів.

Наведена нижче формула дасть мені загальну оцінку Джима з усіх трьох предметів.

= SUM (ІНДЕКС ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

Дозвольте мені пояснити, як працює ця формула.

Хитрість тут полягає у використанні 0 як номера стовпця.

Коли ви використовуєте 0 як номер стовпця у функції INDEX, він повертає всі значення рядків. Аналогічно, якщо ви використовуєте 0 як номер рядка, він поверне всі значення у стовпці.

Отже, нижня частина формули повертає масив значень - {97, 70, 73}

ПОКАЗНИК ($ B $ 2: $ D $ 11, МАТЧ ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Якщо ви просто введете цю формулу вище у клітинку Excel і натиснете Enter, ви побачите #VALUE! помилка. Це тому, що він не повертає єдине значення, а масив значень.

Але не хвилюйтесь, масив значень все ще є. Ви можете перевірити це, вибравши формулу і натиснувши клавішу F9. Він покаже вам результат формули, яка в цьому випадку є масивом із трьох значень - {97, 70, 73}

Тепер, якщо ви включите цю формулу ІНДЕКСУ у функцію SUM, вона дасть вам суму всіх оцінок, набраних Джимом.

Ви також можете використовувати їх, щоб отримати найвищі, найнижчі та середні оцінки Джима.

Так само, як ми зробили це для студента, ви також можете зробити це для предмета. Наприклад, якщо вам потрібен середній бал у предметі, ви можете зберегти номер рядка як 0 у формулі ІНДЕКС, і він дасть вам усі значення стовпців цього предмета.

Натисніть тут, щоб завантажити файл прикладу

Приклад 6: Знайдіть оцінку учня (приблизна техніка поєднання)

Досі ми використовували формулу MATCH, щоб отримати точну відповідність значення пошуку.

Але ви також можете використовувати його для приблизного збігу.

Тепер, що, до біса, є приблизний збіг?

Дозволь пояснити.

Коли ви шукаєте такі речі, як імена чи ідентифікатори, ви шукаєте точну відповідність. Але іноді вам потрібно знати діапазон, у якому лежать ваші значення пошуку. Зазвичай це стосується цифр.

Наприклад, як класний керівник, ви можете знати, яка оцінка кожного учня з предмета, і оцінка визначається на основі балів.

Нижче наведено приклад, коли я хочу, щоб оцінка була для всіх учнів, а оцінка визначається на основі таблиці праворуч.

Отже, якщо учень отримує менше 33, оцінка - F, а якщо він/вона отримує менше 50, але більше 33 - це E тощо.

Нижче наведена формула, яка дозволить це зробити.

= ІНДЕКС ($ F $ 3: $ F $ 8, МАТЧ (B2, $ E $ 3: $ E $ 8,1), 1)

Дозвольте мені пояснити, як працює ця формула.

У функції MATCH ми використовували 1 як аргумент [match_type]. Цей аргумент поверне найбільше значення, яке менше або дорівнює значенню пошуку.

Це означає, що формула MATCH проходить діапазон відміток, і як тільки вона знаходить діапазон відміток, який дорівнює або менше значення знаків пошуку, він зупиниться там і поверне своє місце.

Отже, якщо значення позначки пошуку - 20, функція MATCH поверне 1, а якщо це 85, то поверне 5.

І функція INDEX використовує це значення позиції для отримання оцінки.

ВАЖЛИВО: Щоб це працювало, ваші дані потрібно сортувати у порядку зростання. Якщо це не так, ви можете отримати неправильні результати.

Зверніть увагу, що вищезазначене також можна зробити, використовуючи формулу VLOOKUP нижче:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, ІСТИНА)

Але функція MATCH може піти ще далі, коли йдеться про приблизне збіг.

Ви також можете мати спадні дані та використовувати комбінацію INDEX MATCH для пошуку результату. Наприклад, якщо я зміню порядок таблиці оцінок (як показано нижче), я все одно зможу знайти оцінки учнів.

Для цього мені залишається лише змінити аргумент [match_type] на -1.

Нижче наведена формула, яку я використав:

= ІНДЕКС ($ F $ 3: $ F $ 8, МАТЧ (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP також може виконати приблизну відповідність, але лише тоді, коли дані сортуються у порядку зростання (але це не працює, якщо дані сортуються за спаданням).

Приклад 7: Пошук з урахуванням регістру

Поки всі пошуки, які ми провели, не враховували регістр.

Це означає, що значення для пошуку - Джим, Джим чи Джим - не мало значення. Ви отримаєте той самий результат.

Але що, якщо ви хочете, щоб пошук був чутливим до регістру.

Зазвичай це має місце, коли у вас є великі набори даних і можливість повторення або окремі імена/ідентифікатори (з тією лише різницею)

Наприклад, припустимо, що у мене є наступний набір даних про студентів, де є два учні з іменем Джим (єдина відмінність полягає в тому, що один вводиться як Джим, а інший як Джим).

Зауважте, що є два учні з однаковим ім’ям - Джим (клітина А2 та А5).

Оскільки звичайний пошук не буде працювати, вам потрібно виконати пошук з урахуванням регістру.

Нижче наведена формула, яка дасть вам правильний результат. Оскільки це формула масиву, вам потрібно використовувати Control + Shift + Enter.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

Дозвольте мені пояснити, як працює ця формула.

Функція EXACT перевіряє точну відповідність значення пошуку (яке в цьому випадку є "jim"). Він проходить через усі імена і повертає FALSE, якщо це не збіг, і TRUE, якщо це збіг.

Отже, результат функції EXACT у цьому прикладі - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Зауважте, що є тільки одне ІСТИНА, коли функція EXACT знайшла ідеальну відповідність.

Потім функція MATCH знаходить позицію TRUE у масиві, поверненому функцією EXACT, яка у цьому прикладі дорівнює 4.

Як тільки ми отримаємо позицію, функція INDEX використовує її для пошуку позначок.

Приклад 8: Знайдіть найбільш близьку відповідність

Давайте трохи просунемося зараз.

Припустимо, у вас є набір даних, де ви хочете знайти особу, яка має досвід роботи, найближчий до необхідного досвіду (згадується у клітині D2).

Хоча формули пошуку не створені для цього, ви можете поєднати його з іншими функціями (наприклад, MIN та ABS), щоб це зробити.

Нижче наведена формула, яка знайде людину, яка має досвід, найближчий до необхідної, та поверне ім’я особи. Зауважте, що досвід повинен бути найближчим (який може бути або меншим, або більшим).

= ІНДЕКС ($ A $ 2: $ A $ 15, МАТЧ (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Оскільки це формула масиву, вам потрібно використовувати Control + Shift + Enter.

Хитрість цієї формули полягає в тому, щоб змінити значення пошуку та масив пошуку, щоб знайти мінімальну різницю досвіду в необхідних та фактичних значеннях.

Перш ніж я поясню формулу, давайте зрозуміємо, як ви це зробите вручну.

Ви пройдете кожну клітинку у стовпці В і знайдете різницю в досвіді між тим, що потрібно, і тим, що є у людини. Як тільки ви знайдете всі відмінності, ви знайдете мінімальну та отримаєте ім’я цієї людини.

Це саме те, що ми робимо з цією формулою.

Дозволь пояснити.

Значення пошуку у формулі MATCH-MIN (ABS (D2-B2: B15)).

Ця частина дає вам мінімальну різницю між даним досвідом (що становить 2,5 роки) та всім іншим досвідом. У цьому прикладі він повертає 0,3

Зауважте, що я використовував ABS, щоб переконатися, що шукаю найближчого (що може бути більшим чи меншим за зазначений досвід).

Тепер це мінімальне значення стає нашим значенням пошуку.

Масив пошуку у функції MATCH-ABS (D2- $ B $ 2: $ B $ 15).

Це дає нам масив чисел, з яких було віднято 2,5 (необхідний досвід).

Отже, тепер у нас є значення пошуку (0,3) та масив пошуку ({6,8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

Функція MATCH знаходить позицію 0,3 у цьому масиві, що також є позицією імені особи, яка має найближчий досвід.

Потім цей номер позиції використовується функцією INDEX для повернення імені особи.

Пов'язане читання: Знайдіть найближчий збіг у Excel (приклади з використанням формул пошуку)

Натисніть тут, щоб завантажити файл прикладу

Приклад 9: Використовуйте INDEX MATCH з символами підстановки

Якщо ви хочете шукати значення, коли є часткове збіг, вам потрібно використовувати символи підстановки.

Наприклад, нижче наведено набір даних з назвою компанії та її ринковою капіталізацією, і ви хочете отримати ринкову капіталізацію. дані для трьох компаній справа.

Оскільки це не точні збіги, у цьому випадку ви не можете здійснювати звичайний пошук.

Але ви все ще можете отримати правильні дані, використовуючи зірочку (*), яка є символом підстановки.

Нижче наведена формула, яка надасть вам дані, зіставляючи назви компаній з основного стовпця та отримуючи для них цифру ринкової капіталізації.

= ІНДЕКС ($ B $ 2: $ B $ 10, МАТЧ (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

Дозвольте мені пояснити, як працює ця формула.

Оскільки немає точної відповідності значень пошуку, я використав D2 & ”*” як значення пошуку у функції MATCH.

Зірочка - це символ підстановки, який представляє будь -яку кількість символів. Це означає, що Apple* у формулі означатиме будь -який текстовий рядок, який починається зі слова Apple і може мати будь -яку кількість символів після нього.

Тому, коли Apple* використовується як значення пошуку, а формула MATCH шукає його у стовпці A, він повертає позицію "Apple Inc.", оскільки починається зі слова Apple.

Ви також можете використовувати символи підстановки, щоб знайти текстові рядки, де значення пошуку знаходиться між ними. Наприклад, якщо ви використовуєте * Apple * як значення для пошуку, він знайде будь -який рядок, у якому будь -яке слово містить слово apple.

Примітка: Ця техніка добре працює, якщо у вас є лише один екземпляр відповідності. Але якщо у вас є кілька екземплярів відповідності (наприклад, Apple Inc та Apple Corporation, то функція MATCH поверне лише позицію першого відповідного екземпляра.

Приклад 10: Тристоронній пошук

Це розширене використання INDEX MATCH, але я все одно розкрию його, щоб показати вам силу цієї комбінації.

Пам’ятайте, я сказав, що функція INDEX має два синтаксиси:

= ІНДЕКС (масив, номер ряду, [номер_кол.]) = ІНДЕКС (масив, номер ряду, [номер_кол.], [Номер_області])

Поки що у всіх наших прикладах ми використовували лише перший.

Але для тристороннього пошуку вам потрібно використовувати другий синтаксис.

Спочатку дозвольте мені пояснити, що означає тристоронній погляд.

У двосторонньому пошуку ми використовуємо формулу INDEX MATCH, щоб отримати оцінки, коли ми маємо ім’я учня та назву предмета. Наприклад, отримання оцінок Джима в математиці-це двосторонній пошук.

Тристоронній погляд додасть йому ще одного виміру. Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете дізнатися рахунок Джима з математики на середньостроковому іспиті, тоді це буде тристоронній пошук.

Нижче наведена формула, яка дасть результат.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit test", 1, IF (G $ 3 = "Mid Term", 2,3))))

Наведена вище формула перевіряла три речі - ім’я студента, предмет та іспит. Після того, як він знайде правильне значення, він повертає його в клітинку.

Дозвольте мені пояснити, як працює ця формула, розбивши формулу на частини.

• масив - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): У цьому випадку замість використання одного масиву я використав три масиви в дужках.
• row_num - МАТЧ ($ F $ 5, $ A $ 3: $ A $ 7,0): Функція MATCH використовується для пошуку позиції імені студента у комірці $ F $ 5 у списку імен студента.
• col_num - МАТЧ (G $ 4, $ B $ 2: $ D $ 2,0): Функція MATCH використовується для пошуку позиції імені суб’єкта у комірці $ B $ 2 у списку імені суб’єкта.
• [area_num] - IF (G $ 3 = "Одиничний тест", 1, IF (G $ 3 = "Mid Term", 2,3)): Значення номера області вказує функції INDEX, який із трьох масивів використовувати для отримання значення. Якщо іспит є одиничним терміном, функція IF поверне 1, а функція INDEX буде використовувати перший масив для отримання значення. Якщо іспит середньостроковий, формула IF поверне 2, інакше поверне 3.

Це розширений приклад використання INDEX MATCH, і ви навряд чи знайдете ситуацію, коли вам доведеться це використовувати. Але все -таки добре знати, що можуть зробити формули Excel.

Натисніть тут, щоб завантажити файл прикладу

Чому INDEX/MATCH краще, ніж VLOOKUP?

Або це?

Так, це - в більшості випадків.

Через деякий час я викладу свою справу.

Але перш ніж це зробити, дозвольте мені сказати це - VLOOKUP - надзвичайно корисна функція, і мені це подобається. Він може багато чого робити в Excel, і я час від часу сам ним користуюся. Сказавши це, це не означає, що не може бути нічого кращого, а INDEX/MATCH (з більшою гнучкістю та функціональністю) краще.

Тому, якщо ви хочете виконати базовий пошук, краще скористайтеся VLOOKUP.

INDEX/MATCH - це VLOOKUP для стероїдів. І як тільки ви вивчите INDEX/MATCH, ви завжди можете віддати перевагу його використанню (особливо через його гнучкість).

Не розтягуючи його занадто далеко, дозвольте мені швидко навести вам причини, чому INDEX/MATCH краще, ніж VLOOKUP.

INDEX/MATCH може дивитися вліво (а також праворуч) від значення пошуку

Я висвітлив це в одному з прикладів вище.

Якщо у вас є значення зліва від значення пошуку, ви не можете цього зробити за допомогою VLOOKUP

Принаймні не лише за допомогою VLOOKUP.

Так, ви можете поєднати VLOOKUP з іншими формулами і зробити це, але це стає складним і брудним.

INDEX/MATCH, з іншого боку, зроблено для пошуку всюди (будь то ліворуч, праворуч, угору чи вниз)

INDEX/MATCH може працювати з вертикальними та горизонтальними діапазонами

Знову ж таки, при повній повазі до VLOOKUP, це не зроблено для цього.

Зрештою, V у VLOOKUP означає вертикаль.

VLOOKUP може переглядати дані лише вертикально, тоді як INDEX/MATCH може переглядати дані як вертикально, так і горизонтально.

Звичайно, є функція HLOOKUP, яка подбає про горизонтальний пошук, але це не VLOOKUP … так?

Мені подобається той факт, що комбінація INDEX MATCH досить гнучка для роботи як з вертикальними, так і з горизонтальними даними.

VLOOKUP не може працювати з низхідними даними

Що стосується приблизного збігу, VLOOKUP та INDEX/MATCH знаходяться на одному рівні.

Але INDEX MATCH сприймає суть, оскільки він також може обробляти дані в порядку спадання.

Я показую це в одному з прикладів цього підручника, де ми повинні знайти оцінку учнів на основі таблиці оцінювання. Якщо таблицю відсортовано в порядку спадання, VLOOKUP не працюватиме (але збіг INDEX MATCH буде).

INDEX/MATCH може бути трохи швидшим

Я буду правдивий. Я сам не проводив цей тест.

Я покладаюся на мудрість майстра Excel - Чарлі Кайда.

Різниця в швидкості у VLOOKUP та INDEX/MATCH навряд чи помітна, якщо у вас є невеликі набори даних. Але якщо у вас тисячі рядків і багато стовпців, це може стати вирішальним фактором.

У своїй статті Чарлі Кайд говорить:

«У гіршому випадку метод INDEX-MATCH працює приблизно так само швидко, як і VLOOKUP; у кращому випадку це набагато швидше ».

INDEX/MATCH не залежить від фактичної позиції стовпця

Якщо у вас є набір даних, як показано нижче, коли ви отримуєте оцінку Джима у фізиці, ви можете зробити це за допомогою VLOOKUP.

І для цього ви можете вказати номер стовпця як 3 у VLOOKUP.

Все добре.

Але що, якщо я видалю стовпець Math.

У цьому випадку формула VLOOKUP зламається.

Чому? - Оскільки використання третього стовпця було жорстко закодованим, і коли я видаляю стовпець між ними, третій стовпець стає другим стовпцем.

У цьому випадку використання INDEX/MATCH краще, оскільки ви можете зробити номер стовпця динамічним за допомогою MATCH. Тому замість номера стовпця він перевіряє ім’я теми та використовує його для повернення номера стовпця.

Звичайно, ви можете зробити це, поєднавши VLOOKUP з MATCH, але якщо ви все одно комбінуєте, чому б не зробити це з INDEX, який є набагато гнучкішим.

При використанні INDEX/MATCH ви можете безпечно вставляти/видаляти стовпці у наборі даних.

Незважаючи на всі ці фактори, є причина, чому VLOOKUP настільки популярний.

І це велика причина.

VLOOKUP простіший у використанні

VLOOKUP приймає максимум чотири аргументи. Якщо ви можете обернути голову цими чотирма, ви можете йти.

А оскільки більшість основних справ пошуку також обробляється VLOOKUP, він швидко став найпопулярнішою функцією Excel.

Я називаю це королем функцій Excel.

INDEX/MATCH, навпаки, трохи складніше у використанні. Ви можете повіситися, якщо ви почнете його використовувати, але для початківця VLOOKUP набагато простіше пояснити та вивчити.

І це не гра з нульовою сумою.

Отже, якщо ви новачок у світі пошуку та не знаєте, як користуватися VLOOKUP, краще навчіться цьому.

У мене є докладний посібник із використання VLOOKUP в Excel (з великою кількістю прикладів)

Мій намір у цій статті не спрямовувати дві чудові функції одна проти одної. Я хотів показати вам силу комбінації INDEX MATCH і всі чудові речі, які вона може зробити.

Сподіваюся, ця стаття була вам корисною.

Дайте мені знати ваші думки в розділі коментарів, і якщо ви знайдете якусь помилку в цьому підручнику, будь ласка, дайте мені знати.