Ви можете використовувати функцію Excel MATCH, щоб знайти відносне положення певного значення в діапазоні комірок або масиву.
Функція MATCH схожа на функцію VLOOKUP, оскільки обидві вони віднесені до категорії «Пошук/довідкові функції Excel». VLOOKUP шукає певне значення в стовпці і повертає значення в тому ж рядку, тоді як функція MATCH шукає певне значення в діапазоні та повертає позицію цього значення.
Функція Excel MATCH шукає вказане значення в діапазоні комірок або масиву і повертає відносне положення першого появи цього значення в діапазоні. Функцію MATCH також можна використовувати для пошуку певного значення та повернення йому відповідного значення за допомогою функції INDEX (так само, як Vlookup). Давайте подивимося, як використовувати функцію Excel MATCH, щоб знайти позицію значення пошуку в діапазоні комірок.
Функція Excel MATCH
Функція MATCH є вбудованою функцією в Excel і в основному використовується для визначення відносного розташування значення пошуку в стовпці або рядку.
Синтаксис функції MATCH:
=MATCH(значення_шукання,масив_шукання,[тип_відповідності})де:
lookup_value – Значення, яке потрібно знайти в заданому діапазоні комірок або в масиві. Це може бути числове значення, текстове значення, логічне значення або посилання на клітинку, яке має значення.
пошуковий_масив – Масиви комірок, у яких ви шукаєте значення. Це має бути один стовпець або один рядок.
match_type – Це додатковий параметр, який можна встановити на 0, 1 або -1, а за замовчуванням – 1.
- 0 шукає точну відповідність, коли її не знайдено, повертає помилку.
- -1 шукає найменше значення, яке більше або дорівнює lookup_value, коли масив пошуку в порядку зростання.
- 1 шукає найбільше значення, яке менше або дорівнює значенню look_up, коли масив пошуку в порядку спадання.
Знайти позицію точної відповідності
Припустимо, у нас є наступний набір даних, де ми хочемо знайти позицію певного значення.
У цій таблиці ми хочемо знайти позицію назви міста (Мемфіс) у стовпці (A2:A23), тому ми використовуємо цю формулу:
=MATCH("мемфіс",A2:A23,0)Третій аргумент має значення «0», оскільки ми хочемо знайти точну відповідність назві міста. Як бачите, назва міста «мемфіс» у формулі написана з малого регістру, а в таблиці перша літера назви міста – у верхньому регістрі (Мемфіс). Все-таки формула здатна знайти положення вказаного значення в заданому діапазоні. Це тому, що функція MATCH не чутлива до регістру.
Примітка: Якщо lookup_value не знайдено в діапазоні пошуку або якщо ви вказали неправильний діапазон пошуку, функція поверне помилку #N/A.
Ви можете використовувати посилання на клітинку в першому аргументі функції замість прямого значення. Формула нижче визначає положення значення в клітинці F2 і повертає результат у клітинку F3.
Знайти позицію приблизного збігу
Існує два способи знайти приблизну чи точну відповідність значення пошуку та повернути його позицію.
- Один із способів — знайти найменше значення, яке більше або дорівнює (наступний найбільший збіг) зазначеному значенню. Цього можна досягти, встановивши останній аргумент (match_type) функції як «-1».
- Іншим способом є найбільше значення, яке менше або дорівнює (наступний найменший збіг) заданому значенню. Цього можна досягти, встановивши match_type функції як «1»
Наступний найменший збіг
Якщо функція не може знайти точну відповідність вказаному значенню, коли тип відповідності встановлено на «1», вона знаходить найбільше значення, яке трохи менше зазначеного значення (що означає наступне найменше значення), і повертає його позицію . Щоб це спрацювало, вам потрібно відсортувати масив у порядку зростання, інакше це призведе до помилки.
У прикладі ми використовуємо наведену нижче формулу, щоб знайти наступний найменший збіг:
=СПІВНИК(F2,D2:D23,1)Якщо цій формулі не вдалося знайти точну відповідність для значення в клітинці F2, вона вказує на позицію (16) наступного найменшого значення, тобто 98.
Наступний найбільший матч
Якщо для типу відповідності встановлено значення «-1», а функція MATCH не може знайти точну відповідність, вона знаходить найменше значення, яке перевищує вказане значення (що означає наступне найбільше значення), і повертає його позицію. Для цього методу масив пошуку має бути відсортований у порядку спадання, інакше він поверне помилку.
Наприклад, введіть таку формулу, щоб знайти наступну найбільшу відповідність до значення пошуку:
=СПІВНИК(F2,D2:D23,-1)Ця функція MATCH шукає значення у F2 (55) у діапазоні пошуку D2:D23, і коли не може знайти точну відповідність, вона повертає позицію (16) наступного за величиною значення, тобто 58.
Підстановка відповідності
Підстановкові знаки можна використовувати у функції MATCH, лише якщо match_type встановлено на «0», а значення пошуку є текстовим рядком. Існують символи підстановки, які можна використовувати у функції MATCH: зірочка (*) і знак питання (?).
- Знак питання (?) використовується для узгодження будь-якого окремого символу або літери з текстовим рядком.
- Зірочка (*) використовується для узгодження будь-якої кількості символів із рядком.
Наприклад, ми використали два символи підстановки «?» у lookup_value (Lo??n) функції MATCH, щоб знайти значення, яке відповідає текстовому рядку з будь-якими двома символами (у місцях підстановки). І функція повертає відносне положення відповідного значення в комірці E5.
=MATCH("Lo??n",A2:A22,0)
Ви можете використовувати символ (*) так само, як (?), але зірочка використовується для відповідності будь-якій кількості символів, а знак питання використовується для відповідності будь-якому окремому символу.
Наприклад, якщо ви використовуєте «sp*», функція може збігатися з динаміком, швидкістю або спілбергом тощо. Але якщо функція знайде кілька/дубльованих значень, що відповідають значенню пошуку, вона поверне лише позицію першого значення.
У прикладі ми ввели «Kil*o» в аргумент lookup_value. Отже, функція MATCH() шукає текст, який містить «Kil» на початку, «o» в кінці та будь-яку кількість символів між ними. «Kil*o» відповідає Кіліманджаро в масиві, тому функція повертає відносне положення Кіліманджаро, яке дорівнює 16.
ІНДЕКС і ВІДПОВІДНІСТЬ
Функції MATCH рідко використовуються окремо. Вони часто поєднуються з іншими функціями для створення потужних формул. Коли функцію MATCH об’єднано з функцією INDEX, вона може виконувати розширений пошук. Багато людей все ще вважають за краще використовувати VLOOKUP для пошуку значення, тому що це простіше, але INDEX MATCH гнучкіший і швидший, ніж VLOOKUP.
VLOOKUP може шукати значення лише по вертикалі, тобто у стовпцях, тоді як комбінація INDEX MATCH може виконувати як вертикальний, так і горизонтальний пошук.
Функція INDEX, що використовується для отримання значення в певному місці в таблиці або діапазоні. Функція MATCH повертає відносне положення значення в стовпці або рядку. При об’єднанні MATCH знаходить номер рядка або стовпця (розташування) певного значення, а функція INDEX отримує значення на основі цього рядка та номера стовпця.
Синтаксис функції INDEX:
=INDEX(масив,номер_рядка,[число_стовпця],)У будь-якому випадку давайте подивимося, як працює INDEX MATCH на прикладі.
У наведеному нижче прикладі ми хочемо отримати оцінку «Quiz2» для студентки «Анн». Для цього ми скористаємося наступною формулою:
=ІНДЕКС(B2:F20, ВІДПОВІДАННЯ(H2;A2:A20,0),3)INDEX потребує номер рядка та стовпця, щоб отримати значення. У наведеній вище формулі вкладена функція MATCH знаходить номер рядка (позицію) значення «Anne» (H2). Потім ми надаємо цей номер рядка функції INDEX з діапазоном B2:F20 і номером стовпця (3), який ми вказуємо. А функція INDEX повертає оцінку «91».
Двосторонній пошук із INDEX та MATCH
Ви також можете використовувати функції INDEX і MATCH для пошуку значення в двовимірному діапазоні (двосторонній пошук). У наведеному вище прикладі ми використовували функцію MATCH, щоб знайти номер рядка значення, але ми ввели номер стовпця вручну. Але ми можемо знайти і рядок, і стовпець, вкладаючи дві функції MATCH, одну в аргумент row_num, а іншу в аргумент column_num функції INDEX.
Використовуйте цю формулу для двостороннього пошуку з INDEX і MATCH:
=ІНДЕКС(A1:F20,ВІДПОВІДНІСТЬ(H2,A2:A20,0),СПІВНИК(H3,A1:F1,0))Як відомо, функція MATCH може шукати значення як по горизонталі, так і по вертикалі. У цій формулі друга функція MATCH в аргументі colum_num знаходить позицію Quiz2 (4) і надає її функції INDEX. І INDEX отримує оцінку.
Тепер ви знаєте, як використовувати функцію відповідності в Excel.