ВПР шукає значення в першому стовпці вибраного діапазону і повертає дані з іншого стовпця того самого рядка. В англомовному інтерфейсі Google Таблиць вона записана як VLOOKUP, і вводити можна обидва варіанти назви, результат буде однаковим. Формула замінює ручне гортання прайс-листів, баз клієнтів і складських відомостей там, де потрібно зіставити два масиви за спільним ключем: кодом товару, номером замовлення, ID співробітника. Логіка проста. Саме через цю простоту користувачі рідко помічають обмеження функції, доки не натраплять на помилку в реальній таблиці.
Типовий сценарій такий. Є список товарів із цінами в одному аркуші і список замовлень в іншому. Потрібно підтягнути ціну кожного товару в таблицю замовлень без копіювання вручну. ВПР бере код товару із замовлення, шукає його в прайсі й повертає відповідну ціну з сусіднього стовпця. Якщо прайс оновиться, результат формули перерахується автоматично, без жодних додаткових дій з боку користувача.
Синтаксис функції ВПР
Формула в Google Таблицях виглядає так: =ВПР(шукане_значення; діапазон; номер_стовпця; [точний_збіг]). Латинський варіант =VLOOKUP(...) працює абсолютно ідентично, різниця лише в мові інтерфейсу. Шукане значення це те, що функція розшукує в першому стовпці діапазону: текст, число, дата або посилання на клітинку. Діапазон завжди повинен починатися саме зі стовпця пошуку. Це головна умова, яку новачки порушують найчастіше, і саме вона дає більшість помилок на старті роботи з функцією.
Номер стовпця вказує, з якого стовпця в межах діапазону брати відповідь. Рахунок іде зліва направо, починаючи з 1, а не від адреси аркуша в цілому. Останній аргумент відповідає за тип пошуку, і в переважній більшості завдань його варто ставити на ЛОЖЬ або FALSE.
| Аргумент | Що означає |
|---|---|
| шукане_значення | що шукаємо, зазвичай посилання на клітинку |
| діапазон | таблиця пошуку, перший стовпець якої містить ключ |
| номер_стовпця | звідки брати відповідь, рахунок з 1 |
| точний_збіг | FALSE для точної відповідності, TRUE для наближеної |
Покрокове створення формули
Перш ніж вводити формулу, варто перевірити, що стовпець із ключем пошуку стоїть у діапазоні першим. Це не побажання, а жорстка вимога ВПР. Функція завжди дивиться зліва направо і не вміє шукати у зворотному напрямку без додаткових прийомів, про які піде мова далі в тексті.
- Виберіть клітинку для результату.
- Введіть
=ВПР(і вкажіть клітинку з ключем пошуку. - Виділіть діапазон таблиці, де перший стовпець містить ключ.
- Впишіть номер потрібного стовпця в межах цього діапазону.
- Додайте FALSE як останній аргумент і закрийте дужку.
Після цього формулу можна протягнути вниз за нижній правий кутик клітинки. Google Таблиці автоматично скоригують відносне посилання на ключ пошуку для кожного нового рядка. Діапазон таблиці варто зафіксувати знаком долара, наприклад $A$2:$C$500, інакше під час копіювання формули він поступово зсунеться і почне повертати випадкові значення замість потрібних.
Точний і наближений збіг: у чому різниця
FALSE у четвертому аргументі означає, що функція шукатиме лише повний збіг і поверне помилку, якщо такого значення в першому стовпці немає. Це той режим, який варто використовувати за замовчуванням у більшості таблиць, від прайс-листів до баз клієнтів. TRUE або пропущений аргумент вмикають наближений пошук: формула бере найближче менше значення з відсортованого за зростанням стовпця. Такий режим доречний хіба що для таблиць діапазонів, наприклад коли потрібно визначити знижку залежно від суми покупки або податкову ставку залежно від доходу. Якщо перший стовпець не відсортований, наближений пошук дає непередбачуваний результат. Саме тут ховається значна частина прихованих помилок у чужих таблицях, які на перший погляд працюють коректно.
Типові помилки і як їх виправити
Помилка #Н/Д з’являється, коли точний пошук не знайшов збігу в першому стовпці діапазону. Найчастіша причина, зайвий пробіл у ключі. Друга за поширеністю, невідповідність типів даних, коли число в одній таблиці збережено як текст, а в іншій як число. Перевірити тип можна функцією ЯЧЕЙКА або спробувати помножити підозріле значення на 1: якщо формула видає помилку, перед вами текст.
Помилка #ПОСИЛ! означає, що номер стовпця виходить за межі вказаного діапазону. Помилка #ЗНАЧ! зазвичай свідчить про те, що замість номера стовпця в формулу потрапив текст. Обидві виправляються звіркою аргументів, не переписуванням формули з нуля. Корисна звичка, обгортати ВПР у функцію ЕСЛИОШИБКА, щоб замість коду помилки клітинка показувала порожній рядок або зрозуміле повідомлення на кшталт “немає в базі”.
ARRAYFORMULA і ВПР: формула на весь стовпець без протягування
У Google Таблицях є прийом, який рідко згадують у поясненнях синтаксису ВПР, хоча він економить чи не найбільше часу на практиці. Функція ARRAYFORMULA дозволяє написати формулу один раз у першій клітинці стовпця й одразу застосувати її до всього діапазону, включно з рядками, які додадуть пізніше. Не потрібно протягувати формулу вручну щоразу, коли в таблицю замовлень падає новий рядок. Достатньо один раз ввести конструкцію на кшталт =ARRAYFORMULA(ЕСЛИОШИБКА(ВПР(A2:A; Прайс!A:B; 2; FALSE); "")). Формула сама розшириться на весь діапазон A2:A, а ЕСЛИОШИБКА прибере коди помилок для порожніх рядків унизу таблиці. Обмеження тут одне: на дуже великих діапазонах, від сотні тисяч рядків, обчислення масиву помітно сповільнює аркуш.
Пошук ліворуч: обхід головного обмеження ВПР
Класична скарга на ВПР звучить так: функція шукає лише в стовпцях, розташованих праворуч від ключа. Прямого аргументу для зміни напрямку в ВПР немає. Є обхідний прийом через фігурні дужки: замість звичайного діапазону можна зібрати тимчасовий масив із двох стовпців у потрібному порядку. Формула =ВПР(A2; {C2:C100; A2:A100}; 2; FALSE) поверне значення зі стовпця A за ключем зі стовпця C, хоча в оригінальній таблиці стовпець A стоїть лівіше за C. Цей прийом офіційно описаний у довідці Google Таблиць як спосіб емулювати пошук у зворотному напрямку через ВПР.
ВПР між різними файлами таблиць через IMPORTRANGE
Якщо джерело даних лежить не в тому самому файлі, а в окремій Google Таблиці, пряме посилання на інший файл у формулі ВПР не спрацює. Спершу потрібно імпортувати діапазон із зовнішнього файлу функцією IMPORTRANGE, вказавши URL файлу-джерела і назву аркуша з діапазоном. При першому запуску Google Таблиці попросять підтвердити доступ до зовнішнього файлу, і без цього кроку формула поверне помилку доступу. Після підтвердження результат IMPORTRANGE можна підставити прямо як другий аргумент ВПР, і формула шукатиме дані вже в імпортованому масиві. Такий зв’язок працює, доки в обох файлах не зміниться структура стовпців.
XLOOKUP замість ВПР: нова вбудована функція Google Таблиць
Більшість українських матеріалів про ВПР досі описують її як єдиний практичний інструмент вертикального пошуку в Google Таблицях. З 2022 року в сервісі є вбудована функція XLOOKUP, яка знімає одразу кілька обмежень ВПР. XLOOKUP не потребує номера стовпця. Вона приймає окремо діапазон пошуку й окремо діапазон результату, тому шукає в будь-якому напрямку без фігурних дужок і допоміжних масивів. Формула =XLOOKUP(A2; Прайс!A:A; Прайс!B:B) у більшості випадків замінює аналогічний ВПР без зміни логіки таблиці. Ще одна перевага XLOOKUP, стійкість до вставки нових стовпців усередині джерела: ВПР у такому разі часто ламається, бо номер стовпця зсувається, а XLOOKUP продовжує шукати за посиланням на конкретний стовпець.
Коли обрати INDEX/MATCH замість ВПР
INDEX у парі з MATCH виконує ту саму задачу, що й ВПР, проте розділяє пошук позиції і повернення значення на дві окремі функції. Завдяки цьому формула працює в обидва боки без фігурних дужок і не залежить від порядку стовпців у діапазоні. Синтаксис здається складнішим на перший погляд. Зате така конструкція не ламається при вставці нового стовпця в середину таблиці, бо MATCH шукає позицію за назвою заголовка. Для великих аркушів із частими правками структури це помітно надійніше, ніж перераховувати номер стовпця в кожній формулі ВПР вручну після кожної зміни джерела. Вибір між трьома підходами залежить від того, наскільки часто змінюється структура вихідної таблиці і чи потрібна сумісність зі старими файлами, відкритими також в Excel.