ДомойСтатьиExcelVLOOKUP (ВПР) в англомовному Excel: покроковий гід, XLOOKUP і альтернативи

VLOOKUP (ВПР) в англомовному Excel: покроковий гід, XLOOKUP і альтернативи

VLOOKUP (ВПР) в англомовному Excel: покроковий гід, XLOOKUP і альтернативи
Фото: support.microsoft.com

Цей практичний гід показує, як працює ВПР/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 між українською та англійською

  1. Замінюйте назви функцій за відповідниками: VLOOKUP ↔ ВПР, HLOOKUP ↔ HLOOKUP/ГПР, XLOOKUP ↔ XLOOKUP, INDEX ↔ ІНДЕКС, MATCH ↔ MATCH/ПОИСКПОЗ — у довіднику наведено тримовні відповідності. Для інших мов: VLOOKUP — SVERWEIS (німецька), PROCV (португальська), BUSCARV (іспанська).
  2. Змініть роздільники аргументів: в англійській — кома, у російській та українській часто — крапка з комою. Перекладачі формул також змінюють десятковий роздільник та значення TRUE/FALSE і типи помилок (#N/A).
  3. Не змінюйте тексти в лапках, посилання на клітинки та імена листів — вони залишаються як є.
  4. Пам’ятайте: книга Excel не прив’язана до мови — файл, створений у німецькому Excel, відкриється в англійському без змін формул з боку користувача.
  5. Фіксуйте діапазони абсолютними посиланнями у lookup-аргументах (наприклад, $A$2:$C$100), щоб заповнення формул не зрушувало зону пошуку.
  6. Краще користуватися структурованими посиланнями таблиць (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.

Джерела

- Advertisment -

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Потрібно дізнатися лідерів за показником і не ламати поточне сортування таблиці? Додайте стовпець з рангом у Excel — рядки лишаться на місці, а ви побачите 1‑ше, 2‑ге, 3‑тє місця.

Excel RANDARRAY: як швидко створювати випадкові списки з гнучкими параметрами

Інструкція допоможе зробити у Excel випадкові числа списком — від простого прикладу до гнучкої формули з керуванням довжиною та діапазоном значень. Працює в Excel для Microsoft 365 та інших версіях із підтримкою Spill-функцій.

Як підготувати дані в Excel, щоб Copilot давав кращі відповіді

Copilot працює точніше, коли дані оформлені як таблиця в Excel. Ось як швидко перетворити діапазон на таблицю і які запити давати, щоб отримати надійні підсумки та формули.

Еще записей в тему?
Если честно, некоторые могут быть не свежие:)