
Цей практичний гід показує, як працює ВПР/VLOOKUP в українській та англомовній версіях Excel, як переписати формули на XLOOKUP, коли краще обрати INDEX+MATCH або LOOKUP, і як перекладати формули між мовами без помилок — з реальними прикладами, порадами з продуктивності та сумісності версій.
Що таке ВПР/VLOOKUP: синтаксис, принцип і простий приклад
VLOOKUP шукає значення у першому стовпці таблиці/діапазону і повертає дані з вказаного стовпця з того самого рядка. Синтаксис: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). У найпростішому вигляді: =VLOOKUP(що шукаємо, де шукаємо, номер стовпця для повернення, приблизний чи точний збіг: 1/TRUE або 0/FALSE).
Ключові аргументи: lookup_value — значення для пошуку у першому стовпці table_array; col_index_num — номер стовпця в table_array, починаючи з 1; range_lookup TRUE/пропущено — приблизний збіг, FALSE — точний. Приклад: =VLOOKUP(B2,C2:E7,3,TRUE) — шукає значення з B2 у C2:E7 і повертає значення з третього стовпця цього діапазону.
В українській локалізації VLOOKUP відповідає ВПР і призначена для пошуку в першому стовпці таблиці; приклад синтаксису англійською й українською однаковий за формою: =VLOOKUP(A2,D:F,2,FALSE). У російській версії приклад матиме локалізовану назву та інші роздільники: =ВПР(A2;D:F;2;ЛОЖЬ).
VLOOKUP проти XLOOKUP: головні відмінності і як переписати формулу
XLOOKUP шукає у діапазоні/масиві та повертає елемент, що відповідає першому знайденому збігу; синтаксис: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). Вона використовує окремі lookup_array і return_array, тоді як VLOOKUP використовує єдиний table_array і номер індексу стовпця.
| Можливість | VLOOKUP | XLOOKUP |
|---|---|---|
| Синтаксис | VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]) | XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]) |
| Напрямок пошуку | Тільки праворуч від стовпця пошуку | Повертає з будь-якого боку від стовпця пошуку |
| Тип збігу за замовчуванням | Приблизний (TRUE), якщо аргумент опущено | Точний за замовчуванням (0) |
| Значення «якщо не знайдено» | Немає; повертає #N/A | Аргумент [if_not_found]; без нього — #N/A |
| Режими збігу/пошуку | range_lookup TRUE/FALSE | [match_mode]: 0, -1, 1, 2; [search_mode]: 1, -1, 2, -2 |
| Горизонтальні дані | Потрібен HLOOKUP або інші комбінації | Може замінити HLOOKUP |
Приклад переписування. Було: =VLOOKUP(F2,B2:D11,3,FALSE). Стало: =XLOOKUP(F2,B2:B11,D2:D11,»Not found») — XLOOKUP шукає F2 у B2:B11 і повертає зі стовпця D; якщо не знайдено — «Not found». Інший приклад використання XLOOKUP: =XLOOKUP(«Wireless Mouse», A2:A100, B2:B100) знаходить ціну у стовпці B без потреби сортувати аркуш.
Коли XLOOKUP, а коли INDEX+MATCH, HLOOKUP чи LOOKUP
Якщо стовпець повернення ліворуч від стовпця пошуку, VLOOKUP не підходить; замість нього використовуйте INDEX у поєднанні з MATCH, який знаходить рядок, а INDEX повертає значення зі стовпця. Приклад: MATCH знаходить «Chicago» у B1:B11 (рядок 4), а INDEX повертає населення зі стовпця D для того ж рядка.
HLOOKUP працює так само, як VLOOKUP, але шукає по рядках; приклад: =HLOOKUP(«Axles», A1:C4, 2, TRUE) — шукає у першому рядку і повертає значення з другого рядка того самого стовпця. XLOOKUP може заміняти HLOOKUP, працюючи в будь-якому напрямку.
LOOKUP підходить для сценаріїв з приблизним збігом та «останнім збігом»; приклад: =LOOKUP(F5,item,price). Існує й прийом для пошуку останньої непорожньої клітинки: =LOOKUP(2,1/(B:B<>»»),B:B).
З погляду продуктивності Microsoft радить у багатьох випадках використовувати INDEX+MATCH замість VLOOKUP: попри те, що VLOOKUP трохи швидший і споживає менше пам’яті, комбінація дає більшу гнучкість і може істотно зекономити час перерахунку; INDEX — швидка неволатильна функція. Аргумент wildcard-символів у VLOOKUP доступний для тексту при точному збігу: ? і * (екранування ~).
Покроково: як перекладати формули ВПР/VLOOKUP між українською та англійською
- Замінюйте назви функцій за відповідниками: VLOOKUP ↔ ВПР, HLOOKUP ↔ HLOOKUP/ГПР, XLOOKUP ↔ XLOOKUP, INDEX ↔ ІНДЕКС, MATCH ↔ MATCH/ПОИСКПОЗ — у довіднику наведено тримовні відповідності. Для інших мов: VLOOKUP — SVERWEIS (німецька), PROCV (португальська), BUSCARV (іспанська).
- Змініть роздільники аргументів: в англійській — кома, у російській та українській часто — крапка з комою. Перекладачі формул також змінюють десятковий роздільник та значення TRUE/FALSE і типи помилок (#N/A).
- Не змінюйте тексти в лапках, посилання на клітинки та імена листів — вони залишаються як є.
- Пам’ятайте: книга Excel не прив’язана до мови — файл, створений у німецькому Excel, відкриється в англійському без змін формул з боку користувача.
- Фіксуйте діапазони абсолютними посиланнями у lookup-аргументах (наприклад, $A$2:$C$100), щоб заповнення формул не зрушувало зону пошуку.
- Краще користуватися структурованими посиланнями таблиць (Table structured references) — вони автоматично розширюються і мають менше втрат у продуктивності, ніж посилання на цілі стовпці або динамічні діапазони.
Приклад перекладу: українська =ВПР(A2;Таблиця1[[Код]:[Ціна]];2;FALSE) → англійська =VLOOKUP(A2,Table1[[Code]:[Price]],2,FALSE) — замінено назву функції, роздільники та логічні значення; структуровані посилання збережено.
Типові помилки й як їх виправити
#N/A означає, що формула не може знайти те, що її попрохали знайти; це найчастіше стосується XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP чи MATCH. Рішення: переконайтеся, що значення існує у джерелі, або обгорніть формулу в IFERROR, наприклад =IFERROR(FORMULA(),0) чи з власним текстом =IFERROR(FORMULA(),»Error Message here»). Приклад для XLOOKUP: =IFERROR(XLOOKUP(«Keyboard», A2:A100, B2:B100), «Product not found»).
Неправильні значення з VLOOKUP: якщо range_lookup=TRUE або аргумент опущено і перший стовпець не відсортований, результат може бути неочікуваним; відсортуйте перший стовпець або використовуйте FALSE для точного збігу. За замовчуванням VLOOKUP виконує приблизний збіг, що небезпечно для новачків — можна отримати «нормальні на вигляд», але хибні результати; рекомендується завжди явно задавати останній аргумент: TRUE або FALSE.
У режимі точного збігу VLOOKUP завжди повертає перший збіг — при дублікатаx отримуєте перший з них. У режимі приблизного збігу на відсортованих даних повертається «останній збіг» (найбільше значення ≤ lookup_value).
Типи даних: якщо шукаєте число, а у джерелі воно збережене як текст (або дата як текст), отримаєте #N/A; змініть формат стовпця і скористайтеся Data > Text to Columns > Finish, щоб примусово переформатувати. Для «брудного» тексту використовуйте TRIM всередині VLOOKUP, щоб прибрати пробіли: =VLOOKUP(D2,TRIM(A2:B7),2,FALSE).
Wildcard-символи: при точному пошуку (range_lookup=FALSE) можна використовувати ? і * у lookup_value; для пошуку самих символів — екрануйте ~? або ~*.
Обробка #N/A у точному пошуку на несортованих даних: з Excel 2007 використовуйте IFERROR, напр.: IFERROR(VLOOKUP(lookupval, table, 2, FALSE),0).
Сумісність і продуктивність: версії Excel, поради з прискорення
XLOOKUP недоступна в Excel 2016 і Excel 2019; ви все ж можете зустріти книгу з XLOOKUP, створену у новішій версії. За даними ExcelJet, XLOOKUP доступна лише в Excel 2021+ та Excel 365, тож у старіших версіях не працюватиме.
Для пришвидшення пошуків Microsoft радить: тримати дані й формули на одному аркуші; за можливості спершу сортувати дані й застосовувати приблизний збіг; для точного збігу обмежувати діапазон пошуку (таблиці, структуровані посилання, динамічні імена). У версії Office 365 1809+ точні VLOOKUP/HLOOKUP/MATCH по кількох стовпцях із того самого діапазону стали значно швидшими. На відсортованих даних два приблизні VLOOKUP інколи значно швидші за один точний (межа ефективності біля 10–20 рядків).
VLOOKUP у точному режимі може бути повільним на великих наборах, тоді як приблизний дуже швидкий; XLOOKUP/XMATCH мають вбудований бінарний пошук для супер-швидких збігів. Складні динамічні масивні формули можуть перераховуватись при будь-яких змінах аркуша, тоді як PivotTable використовує кеш і оновлюється за Refresh.
Коли даних забагато для аркуша, Data Model у Power Pivot стискає й утримує мільйони рядків, тоді як лист обмежений 1,048,576 рядків (публікація від 11 серпня 2026 року). Power Query — інструмент підготовки даних на вкладці Дані (Data) у групі Get & Transform Data; він записує кроки трансформацій і відтворює їх при Refresh; під капотом — мова M (публікація від 11 серпня 2026 року). Автор How‑To Geek описує, як з’єднав папку в Power Query, щоб автоматично консолідувати файли: досить покласти новий у папку і оновити книгу; налаштування зайняли кілька хвилин і прибрали рутину (публікація від 4 листопада 2026 року).
Практичні кейси: готові формули українською й англійською
1) «Лівобічний» пошук через INDEX+MATCH
Сценарій: шукаємо місто у середньому стовпці і повертаємо значення з лівішого/правішого стовпця. Приклад: MATCH знаходить «Chicago» у B1:B11 (рядок 4), а INDEX повертає населення зі стовпця D.
- Англійська: =INDEX(D1:D11, MATCH(«Chicago», B1:B11, 0))
- Українська: =INDEX(D1:D11, MATCH(«Chicago», B1:B11, 0))
2) XLOOKUP повертає кілька стовпців одразу
XLOOKUP може повернути масив із кількома елементами — наприклад, ім’я співробітника і відділ однією формулою.
- Англійська: =XLOOKUP(F5, B5:B14, C5:D14)
- Українська: =XLOOKUP(F5, B5:B14, C5:D14)
3) Підсумок за інтервалом через дві XLOOKUP
Приклад суми між двома позиціями: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10)).
- Англійська: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
- Українська: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
4) Остання ціна/останнє непорожнє значення
LOOKUP для останнього збігу в не відсортованих даних: =LOOKUP(2,1/(item=F5),price). Остання непорожня клітинка в стовпці: =LOOKUP(2,1/(B:B<>»»),B:B).
- Англійська: =LOOKUP(2,1/(item=F5),price); =LOOKUP(2,1/(B:B<>»»),B:B)
- Українська: =LOOKUP(2,1/(item=F5),price); =LOOKUP(2,1/(B:B<>»»),B:B)
Додаткові приклади й короткі підказки
- Приклад VLOOKUP із приблизним збігом: =VLOOKUP(1,A2:C10,2) — знаходить найбільше значення ≤ 1 у стовпці A (0,946) і повертає значення зі стовпця B з того ж рядка.
- Приклади синтаксису для Mary: =LOOKUP(E2,A2:A5,C2:C5) та =VLOOKUP(E2,A2:C5,3,FALSE) обидві знаходять вік Mary (22).
- У XLOOKUP параметр [if_not_found] дозволяє показати дружнє повідомлення, інакше за відсутності збігу повертає #N/A.
Power Query та великі дані: де межа для формул пошуку
Коли обсяги перевищують можливості аркуша (1 048 576 рядків), працюйте з Data Model через Power Pivot або попередньо готуйте дані в Power Query — інструменті на вкладці Дані, який записує кроки трансформацій і відтворює їх при Refresh (матеріал від 11 серпня 2026 року). З’єднання папки в Power Query дозволяє автоматично підхоплювати й консолідувати нові файли у звіт (матеріал від 4 листопада 2026 року).
Чек-лист перед запуском VLOOKUP/XLOOKUP
- Потрібен пошук ліворуч або кілька стовпців повернення? Оберіть XLOOKUP або INDEX+MATCH.
- Старі версії Excel у колег? Переконайтесь, що XLOOKUP доступний, інакше — VLOOKUP або INDEX+MATCH.
- Дані відсортовані? Якщо так — приблизний збіг дасть приріст швидкості; якщо ні — використовуйте точний збіг і обмежуйте діапазон.
- Очікувані відсутні значення? Додайте IFERROR/IFNA для обробки #N/A.
- Текст «брудний» або типи змішані? Очистіть TRIM, виправте типи або скористайтеся Data > Text to Columns > Finish.
Джерела
- support.microsoft.com: VLOOKUP function
- support.microsoft.com: XLOOKUP function
- officeskill.org: Усі формули Excel англійською, українською та російською мовами: повний довідник функцій Excel (2026-06-01)
- exceljet.net: XLOOKUP vs VLOOKUP (2023-04-02)
- support.microsoft.com: Use Excel built-in functions to find data in a table or a range of cells
- exceljet.net: VLOOKUP Function (2012-12-28)
- support.microsoft.com: Look up values with VLOOKUP, INDEX, or MATCH
- How-To Geek: Excel: 6 essential Excel formulas every beginner should learn first (2026-10-26)
- exceljet.net: How to lookup first and last match (2019-03-21)
- learn.microsoft.com: Excel performance — Tips for optimizing performance obstructions
- cellbycell.app: Excel formula translator for 16 languages
- support.microsoft.com: How to correct a #N/A error
- MakeUseOf: Excel: The new Excel functions everyone loves still can’t do what a pivot table does (2026-10-16)
- Trump Excel: Power Query vs Power Pivot vs Power BI (2026-08-11)
- How-To Geek: Excel: Excel alone is great. Excel plus these 5 apps is even more powerful (2026-11-04)



