INDEX MATCH в Excel: формула, приклади та типові помилки

By Mirko Peters · 2026-11-12

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

Automatically translated with AI from the original post. Read the English original

INDEX MATCH в Excel — це формула пошуку, побудована з двох функцій: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH знаходить, де значення розташоване у списку, а INDEX повертає елемент із тієї самої позиції в іншому списку. Ця стаття призначена для користувачів Excel, які пишуть формули VLOOKUP і стикаються з його обмеженнями, або успадкували книгу з великою кількістю INDEX MATCH і хочуть її зрозуміти. Ви побачите кожну функцію окремо, як вони працюють разом, як шукати ліворуч, як виконати двовимірний пошук, як використовувати два критерії та що означають помилки.

Головне

• INDEX MATCH — це дві функції в одній формулі: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH знаходить позицію, а INDEX повертає елемент на цій позиції.
• Використовуйте 0 як третій аргумент MATCH для точного збігу. Якщо залишити значення за замовчуванням 1, MATCH вважатиме, що дані відсортовані за зростанням.
• Оскільки діапазони пошуку й повернення розділені, INDEX MATCH може шукати ліворуч від стовпця пошуку, чого не може VLOOKUP.
• Для двох умов або об'єднайте стовпці в допоміжному стовпці, або використайте формулу, яка перемножує два порівняння всередині MATCH.
• Загорніть формулу в IFNA, щоб замінити #N/A на повідомлення, а інші помилки все одно відображалися. Так реальні помилки не приховаються.

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

Що робить INDEX

INDEX повертає значення з позиції всередині діапазону. Сторінка Microsoft подає форму для масиву як INDEX(array, row_num, [column_num]).

• array — це діапазон, з якого береться значення.
• row_num — це рядок усередині цього діапазону.
• column_num — це стовпець усередині цього діапазону. Якщо діапазон складається лише з одного стовпця, його можна не вказувати.

Числа відраховуються від верхньої лівої клітинки діапазону, а не від аркуша. В прикладі Microsoft блок має Apples і Bananas у першому стовпці, а Lemons і Pears — у другому. =INDEX(A2:B3,2,2) повертає Pears, а =INDEX(A2:B3,2,1) повертає Bananas.

Два правила з тієї самої сторінки варто запам'ятати. Якщо row_num або column_num вказують за межі діапазону, INDEX повертає помилку #REF!. А якщо встановити row_num або column_num рівним 0, INDEX поверне весь стовпець або весь рядок. У поточних версіях Microsoft 365 формулу вводять звичайним натисканням Enter. У старіших версіях її вводять як формулу масиву комбінацією Ctrl+Shift+Enter.

Сам по собі INDEX потребує, щоб ви вже знали позицію. Саме тут у пригоді MATCH.

Що робить MATCH

MATCH шукає в діапазоні та повертає позицію. Microsoft подає синтаксис як MATCH(lookup_value, lookup_array, [match_type]). Він не повертає сам елемент, лише його відносну позицію в діапазоні.

Третій аргумент визначає спосіб збігу:

• 0 знаходить перше точне співпадіння. Діапазон може бути в будь-якому порядку. Цей варіант ви використовуватимете найчастіше.
• 1, який також є значенням за замовчуванням, якщо аргумент пропущено, знаходить найбільше значення, яке менше або дорівнює шуканому. Діапазон має бути відсортований за зростанням.
• -1 знаходить найменше значення, яке більше або дорівнює шуканому. Діапазон має бути відсортований за спаданням.

В прикладах Microsoft використовуються значення 25, 38, 40 і 41 у діапазоні B2:B5. =MATCH(39,B2:B5,1) повертає 2, позицію значення 38, найближчого меншого значення. =MATCH(41,B2:B5,0) повертає 4. А =MATCH(40,B2:B5,-1) повертає #N/A, оскільки ці значення не відсортовані за спаданням.

Ще кілька особливостей з тієї ж сторінки: пошук тексту не враховує регістр, тож «apple» і «APPLE» вважаються однаковими. За типу співпадіння 0 і текстового шуканого значення ? відповідає будь-якому одному символу, а * відповідає будь-якій послідовності символів. Тильда перед будь-яким із них означає пошук саме цього символу. Якщо MATCH нічого не знаходить, він повертає #N/A.

Значення за замовчуванням 1 часто збиває з пантелику. Якщо ви забудете третій аргумент, MATCH без попередження вважатиме, що дані відсортовані, і може повернути неправильну позицію без жодної помилки. Майже в кожному пошуку за ідентифікаторами, іменами чи кодами вводьте 0.

З'єднаймо все разом

Схема завжди однакова: MATCH вкладається всередину INDEX і передає номер рядка.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Візьмемо аркуш з ідентифікаторами працівників у A2:A100, іменами в B2:B100 та відділами в C2:C100. Ви вводите ідентифікатор у F2 і хочете отримати відділ.

1. Клацніть клітинку для результату, наприклад G2.
2. Введіть =INDEX(C2:C100,, щоб вказати, звідки береться відповідь.
3. Додайте MATCH(F2,A2:A100,0)), щоб вказати, де шукати ідентифікатор.
4. Натисніть Enter.

Готова формула має вигляд =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH знаходить F2 в A2:A100 і повертає, наприклад, 14. Потім INDEX повертає 14-й елемент у C2:C100.

Діапазони мають бути однакової довжини та починатися з того самого рядка. Якщо MATCH шукає в A2:A100, а INDEX читає C3:C101, позиція вказуватиме на інший рядок, і відповідь буде неправильною без жодного попередження. Використовуйте для обох однакові початковий і кінцевий рядки та зафіксуйте їх знаками долара, $A$2:$A$100, якщо плануєте копіювати формулу вниз.

Пошук ліворуч і відмінності від VLOOKUP

На сторінці VLOOKUP від Microsoft синтаксис подано як VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Там сказано, що шукане значення має бути в першому стовпці діапазону, а якщо опустити range_lookup, отримаємо наближений пошук, який припускає відсортовані дані. На сторінці також пропонують спробувати XLOOKUP.

Ось та сама задача двома способами, де ідентифікатори у стовпці A, а імена у стовпці B:

• =VLOOKUP(F2,A2:B100,2,FALSE)
• =INDEX(B2:B100,MATCH(F2,A2:A100,0))

INDEX MATCH має дві реальні переваги. По-перше, він уміє шукати ліворуч. Якщо імена знаходяться в стовпці B, а ви хочете отримати ідентифікатор зі стовпця A, достатньо поміняти діапазони місцями: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP так не вміє, адже шукане значення має бути в першому стовпці. По-друге, він напряму вказує стовпець для результату, а не рахує його. У VLOOKUP цифра 2 у формулі означає «другий стовпець діапазону». Якщо вставити стовпець усередину діапазону, число почне вказувати на інший стовпець. У INDEX MATCH посилання змінюється так само, як будь-яке інше посилання.

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

Двовимірний пошук

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

=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))

Тут B2:M20 містить числа, A2:A20 — назви товарів, B1:M1 — заголовки місяців, а P1 і Q1 містять товар і місяць, які вам потрібні. Перший MATCH дає рядок, другий — стовпець, а INDEX повертає число на їх перетині. Зверніть увагу, що діапазон повернення починається з B2, а не з A1, щоб позиції збігалися з діапазонами міток: обидва діапазони MATCH також починаються з того самого рядка або стовпця, що й числа.

Пошук за двома критеріями

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

Допоміжний стовпець. Додайте стовпець, який об’єднує два поля, наприклад =A2&"|"&B2 у D2, і протягніть формулу вниз. Далі шукайте об’єднаний текст:

=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))

Роздільник має значення. Без нього "AB" плюс "C" і "A" плюс "BC" дають однаковий результат "ABC". Допоміжний стовпець легко читається й перевіряється, і працює в усіх версіях.

Формула масиву. Без допоміжного стовпця можна перемножити два порівняння:

=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))

Кожне порівняння дає TRUE або FALSE. Множення перетворює їх на 1 лише там, де обидві умови виконуються, а MATCH шукає перші 1. Цього немає на сторінках Microsoft про INDEX і MATCH, тож перевірте формулу на невеликому діапазоні. У старіших версіях Excel її може знадобитися ввести комбінацією Ctrl+Shift+Enter, так само як Microsoft описує для результатів-масивів з INDEX. У поточних версіях Microsoft 365 достатньо Enter.

Шаблони та найближчі значення

Із типом збігу 0 і текстом MATCH підтримує шаблонні символи. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) повертає елемент поруч із першим записом, що починається з "Jan". Це зручно для часткових кодів. Робіть шаблон конкретним, бо MATCH зупиняється на першому збігу.

Для рівнів, наприклад податкових шкал або комісійних ставок, навмисно використовуйте типовий тип збігу 1, а порогові значення мають бути відсортовані за зростанням. Помістіть 0, 1000 і 5000 у A2:A4, а ставки — у B2:B4. Тоді =INDEX(B2:B4,MATCH(E2,A2:A4,1)) поверне ставку найбільшого порогу, який не перевищує E2. Це одне з небагатьох завдань, де відсортована поведінка за замовчуванням є правильним вибором.

Виправлення помилок

• #N/A. MATCH нічого не знайшов. Шукайте друкарські помилки, пробіли в кінці та числа, збережені як текст, адже "100" і 100 — різні значення. Перевірте, що тип збігу дорівнює 0, а типи для відсортованих даних (1 або -1) справді працюють із відсортованими даними.
• #REF!. INDEX запитав рядок або стовпець поза діапазоном. Це також трапляється, коли ви видаляєте рядок або стовпець, які використовує формула. Перевірте, що діапазон MATCH і діапазон INDEX мають однаковий розмір.
• Неправильна відповідь без жодної помилки. Зазвичай це пропущений 0 у MATCH або діапазони, що починаються з різних рядків.
• Неправильна відповідь після копіювання вниз. Діапазони змістилися, бо їх не зафіксовано. Додайте $ до діапазонів.

Щоб замінити помилку повідомленням, функція IFNA від Microsoft повертає значення, яке ви вкажете, коли формула дає #N/A, а всі інші помилки передає без змін:

=IFNA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Не знайдено")

Для пошуку краще використовувати IFNA, а не IFERROR. IFERROR приховує всі помилки, зокрема #REF!, спричинену пошкодженим діапазоном, тож справжня несправність виглядає як звичайне «Не знайдено». IFNA охоплює лише очікуваний випадок. Microsoft указує IFNA для Excel 2016 і новіших версій.

INDEX MATCH чи XLOOKUP

Якщо всі, хто відкриває ваш файл, мають актуальний Microsoft 365, XLOOKUP виконує ту саму роботу однією функцією та має вбудоване повідомлення про відсутність значення. Ми розглядаємо її в нашому посібнику з XLOOKUP в Excel. Але сторінка Microsoft про XLOOKUP не називає чітко Excel 2016 і 2019 і не згадує Excel для вебу. INDEX MATCH працює в усіх них. Тож вибір практичний: використовуйте XLOOKUP у файлах для поточних версій, а INDEX MATCH — коли файл передається людям зі старішими версіями або коли ви підтримуєте книгу, яка вже його використовує.

Типові помилки

• Пропуск 0 у MATCH і довіру типовому значенню.
• Використання діапазонів різного розміру або з різними початковими рядками для INDEX і MATCH.
• Забування зафіксувати діапазони символом $ перед копіюванням формули вниз.
• Об’єднання критеріїв без роздільника в допоміжному стовпці.
• Загортання всього в IFERROR, через що зламані формули виглядають як відсутні дані.
• Очікуєте, що INDEX MATCH поверне всі збіги. Він повертає лише перший.

Пов'язані теми для вивчення

• XLOOKUP. Новіша функція пошуку з вбудованими аргументами напряму пошуку та значення, якщо нічого не знайдено.
• Кілька результатів одночасно. INDEX MATCH повертає лише перший збіг. Для повернення всіх збігів у версіях, які її підтримують, призначена функція FILTER.
• Таблиці та іменовані діапазони. Структуровані посилання та імена роблять пошук зрозумілішим для читання і дозволяють діапазону зростати разом із даними.
• Продуктивність на великих аркушах. Використовуйте обмежені діапазони замість цілих стовпців і тримайте діапазони компактними.
• Пошук з урахуванням регістру. MATCH ігнорує регістр, тому для пошуку з урахуванням регістру потрібен інший підхід. Перевірте будь-який обхідний варіант, перш ніж покладатися на нього.

Наступний крок

Перепишіть одну з ваших формул VLOOKUP як INDEX MATCH і порівняйте результат на кількох відомих значеннях. Потім спробуйте ту саму формулу з пошуком ліворуч. Виконавши обидва варіанти, ви зрозумієте, коли INDEX MATCH в Excel є кращим інструментом для завдання.