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

• Функція INDEX
• Функція МАТЧ
• Поєднайте INDEX і MATCH

• Двосторонній пошук
• Пошук зліва
• Пошук з урахуванням регістру
• Найближчий збіг
• Пошук за кількома критеріями
• Пошук у кількох стовпцях
• Пошук першого непорожнього значення
• Пошук першого числового значення
• Пошук MAX або MIN асоціацій
• Порада: налаштуйте власні повідомлення про помилку #N/A

Як використовувати INDEX і MATCH в Excel

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

Як використовувати функцію INDEX в Excel

Команда ІНДЕКС функція в Excel повертає значення в заданому місці в певному діапазоні. Синтаксис функції INDEX такий:

• масив (обов’язковий) відноситься до діапазону, з якого потрібно повернути значення.
• номер_рядка (обов'язково, якщо не номер_столбця присутній) посилається на номер рядка масиву.
• номер_столбця (необов'язково, але обов'язково, якщо номер_рядка опущено) посилається на номер стовпця масиву.

Наприклад, знати оцінка Джеффа, 6для студента у списку, ви можете використовувати функцію INDEX таким чином:

=INDEX(C2:C11,6)

√ Примітка: діапазон C2: C11 там, де перераховані бали, а кількість 6 знаходить екзаменаційний бал 6й студент.

Ось давайте зробимо невеличкий тест. Для формули =ІНДЕКС(A1:C1,2;XNUMX), яке значення він поверне? --- Так, повернеться Дата народження, 2значення в даному рядку.

Тепер ми повинні знати, що функція INDEX може ідеально працювати з горизонтальними або вертикальними діапазонами. Але що, якщо нам потрібно повернути значення в більшому діапазоні з кількома рядками і стовпцями? Ну, в цьому випадку ми повинні застосувати і номер рядка, і номер стовпця. Наприклад, щоб дізнатися Оцінка Джеффа в межах діапазону таблиці замість одного стовпця ми можемо знайти його оцінку за допомогою a рядок номер 6 і колонка номер 3 в клітини від A2 до C11 подобається це:

=INDEX(A2:C11,6,3)

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

Дивовижний! Використання ефективних вкладок у Excel, таких як Chrome, Edge, Firefox і Safari!
Заощаджуйте 50% свого часу та зменшуйте тисячі клацань миші щодня!

Як використовувати функцію MATCH в Excel

Функція MATCH в Excel повертає числове значення, розташування певного елемента в заданому діапазоні. Синтаксис функції MATCH виглядає наступним чином:

• lookup_value (обов’язково) посилається на значення, яке потрібно зіставити в пошуковий_масив.
• пошуковий_масив (обов’язковий) відноситься до діапазону комірок, у яких потрібно шукати MATCH.
• match_type (За бажанням): 1, 0 or -1.
  • 1 (за замовчуванням) MATCH знайде найбільше значення, яке менше або дорівнює lookup_value. Значення в пошуковий_масив повинні бути розміщені в порядку зростання.
  • 0, MATCH знайде перше значення, яке точно дорівнює lookup_value. Значення в пошуковий_масив може бути в будь-якому порядку. (У випадках, коли тип відповідності встановлено на 0, ви можете використовувати символи підстановки.)
  • -1, MATCH знайде найменше значення, яке більше або дорівнює lookup_value. Значення в пошуковий_масив повинні бути розміщені в порядку спадання.

Наприклад, знати місце Віри в іменному списку, ви можете використовувати формулу MATCH так:

=MATCH("Vera",A2:A11,0)

√ Примітка: результат «4» вказує на те, що ім'я «Віра» знаходиться на 4 позиції списку.

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

Як поєднати INDEX і MATCH в Excel

Перегляньте приклад нижче, щоб зрозуміти, як ми можемо поєднати функції INDEX і MATCH:

Знайти Оцінка Евелін, знаючи, що оцінки за іспит знаходяться в 3rd стовпець, ми можемо використовуйте функцію MATCH для автоматичного визначення позиції рядка без необхідності підраховувати його вручну. Згодом ми можемо використати функцію INDEX для отримання значення на перетині ідентифікованого рядка та 3-го стовпця:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

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

Команда ІНДЕКС формула містить три аргументи:

• номер_рядка: MATCH("Евелін";A2:A11,0;XNUMX) надає INDEX позицію рядка значення "Евелін" в діапазоні A2: A11, Яка є 5.
• номер_столбця: 3 вказує 3rd стовпець для INDEX, щоб знайти оцінку в масиві.
• масив: A2: C11 наказує INDEX повернути відповідне значення на перетині вказаного рядка та стовпця в межах діапазону від A2 до C11. Нарешті отримуємо результат 90.

У наведеній вище формулі ми використали жорстко закодоване значення, "Евелін". Однак на практиці жорстко закодовані значення є непрактичними, оскільки вони вимагатимуть модифікації щоразу, коли ми шукатимемо інші дані, наприклад оцінку іншого студента. У таких сценаріях ми можемо використовувати посилання на клітинки для створення динамічних формул. Наприклад, у цьому випадку я буду змінити "Evelyn" на F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Приклади формул INDEX і MATCH

У цій частині ми поговоримо про різні обставини використання функцій INDEX і MATCH для задоволення різних потреб.

INDEX і MATCH, щоб застосувати двосторонній пошук

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

У таких випадках ми можемо виконати двосторонній пошук, також відомий як пошук матриці, використовуючи дві функції MATCH: одну для пошуку номера рядка, а іншу для визначення номера стовпця. Наприклад, знати Оцінка Евелін, ми повинні використовувати формулу:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX і MATCH, щоб застосувати пошук зліва

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

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

Знайти Клас Евелін, використовуйте таку формулу, щоб шукати Евелін у B2:B11 та отримати відповідне значення з A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX і MATCH, щоб застосувати пошук з урахуванням регістру

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

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

Наприклад, знати Оцінка екзамену ДЖІММІ, використовуйте таку формулу:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у , крім Excel 365 і Excel 2021.

INDEX і MATCH, щоб знайти найближчий відповідник

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

• масив посилається на діапазон, з якого потрібно повернути значення.
• пошуковий_масив відноситься до діапазону значень, до якого потрібно знайти найближчу відповідність lookup_value.
• lookup_value посилається на значення, щоб знайти його найближчу відповідність.

Наприклад, щоб дізнатися чия оцінка найближча до 85, використовуйте таку формулу, щоб пошук найближчого результату до 85 у C2:C11 та отримати відповідне значення з A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у , крім Excel 365 і Excel 2021.

Дивовижний! Використання ефективних вкладок у Excel, таких як Chrome, Edge, Firefox і Safari!
Заощаджуйте 50% свого часу та зменшуйте тисячі клацань миші щодня!

INDEX і MATCH, щоб застосувати пошук за кількома критеріями

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

√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у . Тоді в рядку формул з’явиться пара фігурних дужок.

• масив посилається на діапазон, з якого потрібно повернути значення.
• (lookup_value=lookup_array) представляє одну умову. Ця умова перевіряє, чи певний lookup_value відповідає значенням у пошуковий_масив.

Наприклад, щоб знайти оцінка Коко з класу А, дата народження якої 7, ви можете використовувати таку формулу:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX і MATCH, щоб застосувати пошук у кількох стовпцях

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

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

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Ось де Kutools для Excel's Індекс і відповідність у кількох стовпцях функція стане в нагоді. Це спрощує процес, дозволяючи швидко та легко зіставляти конкретні записи з відповідними категоріями. Щоб розблокувати цей потужний інструмент і без особливих зусиль порівняти Шона з його класом, просто завантажте та встановіть надбудову Kutools for Excel, а потім виконайте наступне:

1. Виберіть комірку призначення, де потрібно відобразити відповідний клас.
2. на Кутулс вкладка, клацніть Помічник формули > Пошук та довідкові матеріали > Індекс і відповідність у кількох стовпцях.
3. 
3. У спливаючому діалоговому вікні виконайте такі дії:
   1. Натисніть 1-й кнопка поруч із Пошук_кол щоб вибрати стовпець, що містить ключову інформацію, яку ви хочете повернути, тобто імена класів. (Тут можна вибрати лише один стовпець.)
   2. Натисніть 2-й кнопка поруч із Table_rng щоб вибрати клітинки, які відповідають значенням у вибраному Пошук_кол, тобто імена студентів.
   3. Натисніть 3-й кнопка поруч із Пошук_значення щоб вибрати клітинку, що містить ім’я учня, якого потрібно зіставити з його класом, у даному випадку це Шон.
   4. Натисніть OK.
   5. 

Результат

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

INDEX і MATCH для пошуку першого непорожнього значення

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

• Отримати перше непорожнє значення в стовпці чи рядку, ігноруючи помилки:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Отримати перше непорожнє значення в стовпці чи рядку з помилками:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX і MATCH для пошуку першого числового значення

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

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX і MATCH для пошуку MAX або MIN асоціацій

Якщо вам потрібно отримати значення, пов’язане з максимальним або мінімальним значенням у діапазоні, ви можете використовувати функцію MAX або MIN разом із функціями INDEX і MATCH.

• INDEX і MATCH для отримання значення, пов’язаного з максимальним значенням:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX і MATCH для отримання значення, пов’язаного з мінімальним значенням:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• У наведених вище формулах є два аргументи:
• • масив посилається на діапазон, з якого потрібно повернути відповідну інформацію.
  • пошуковий_масив представляє набір значень, які потрібно перевірити або шукати за конкретними критеріями, тобто максимальні чи мінімальні значення.

Наприклад, якщо ви хочете визначити у кого найвищий бал, використовуйте таку формулу:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Порада: налаштуйте власні повідомлення про помилку #N/A

Під час роботи з функціями Excel INDEX і MATCH ви можете зіткнутися з помилкою #N/A, якщо немає відповідного результату. Наприклад, у наведеній нижче таблиці під час спроби знайти оцінку студентки на ім’я Саманта з’являється помилка #N/A, оскільки її немає в наборі даних.

Щоб зробити ваші електронні таблиці зручнішими для користувача, ви можете налаштувати це повідомлення про помилку, включивши свою формулу INDEX MATCH у функцію IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Вище наведено весь відповідний вміст, пов’язаний із функціями INDEX і MATCH у Excel. Сподіваюся, підручник буде для вас корисним. Якщо ви хочете ознайомитися з іншими порадами та підказками щодо Excel, Будь ласка, натисніть тут щоб отримати доступ до нашої великої колекції з понад тисячі посібників.