ВПР в Google Таблицах: формула, примеры, XLOOKUP

Коротко: как работает ВПР

ВПР — вертикальный просмотр. Функция берёт искомое значение, сверху вниз ищет его в первом столбце диапазона и возвращает значение из столбца с указанным номером в найденной строке. Если совпадений несколько, возвращается первое.

• Синтаксис: =ВПР(запрос; диапазон; индекс; [отсортировано]).
• Четвёртым аргументом всегда пишите ЛОЖЬ: по умолчанию стоит ИСТИНА, и ВПР ищет приблизительно.
• #Н/Д означает, что ключа нет в первом столбце: опечатка, лишний пробел или число, сохранённое как текст.
• Искать влево ВПР не умеет; в Google Таблицах для этого есть XLOOKUP, ИНДЕКС с ПОИСКПОЗ или массив {B:B \ A:A}.
• На весь столбец одной формулой — через ARRAYFORMULA.

Учебные данные: заказы и справочник клиентов

Данные — лист «Заказы» серии «Google Таблицы для аналитика»: пятнадцать заказов за сентябрь и октябрь 2026 года, столбцы A–G — дата, номер заказа, клиент, канал, город, сумма и статус. Полностью лист приведён в статье о функции QUERY. Оплаченных заказов двенадцать на 58 500 ₽.

Второй лист — «Клиенты»: шесть клиентов, за каждым закреплён менеджер и сегмент. Справочник отсортирован по имени, это понадобится в разделе о четвёртом аргументе. В справочнике нет Новикова: он сделал первый заказ 26 октября, и в справочник его ещё не внесли. Такой разрыв между данными и справочником встречается в любой компании.

Синтаксис ВПР в Google Таблицах

«Запрос» — что ищем, обычно ссылка на ячейку: клиент заказа в C2. «Диапазон» — справочник, в первом столбце которого лежат ключи: Клиенты!$A$2:$C$7. «Индекс» — номер столбца внутри диапазона, считая с единицы: менеджер — 2, сегмент — 3. «Отсортировано» — ЛОЖЬ для точного совпадения.

Формула в H2 листа «Заказы»: =ВПР(C2; Клиенты!$A$2:$C$7; 2; ЛОЖЬ). Для первого заказа Орловой она вернёт «Ахмедова». Знаки доллара закрепляют справочник: формулу протягивают вниз, и без них диапазон уползёт вместе с ней. Вместо протягивания можно поставить одну формулу на весь столбец — об этом ниже.

Номер столбца — слабое место ВПР. Если в справочник вставят столбец «Телефон» между клиентом и менеджером, индекс 2 начнёт возвращать телефоны, и ошибки не будет. Если индекс больше ширины диапазона, ВПР вернёт #ССЫЛ!, а если меньше единицы — #ЗНАЧ!.

Выручка по менеджерам: формула и результат

После ВПР у каждого заказа появился менеджер, и дальше работает обычная СУММЕСЛИ или сводная таблица. По оплаченным заказам Ахмедова ведёт клиентов на 35 500 ₽, Сергеев — на 17 900 ₽.

Заказ Новикова на 5 100 ₽ получил #Н/Д. Это правильная реакция: клиента нет в справочнике, и формула об этом говорит. Хуже, если #Н/Д спрятать: ЕСЛИОШИБКА(ВПР(…); "") превратит ошибку в пустую ячейку, и 5 100 ₽ молча выпадут из отчёта по менеджерам. Лучше подставлять текст, который видно и по которому можно отфильтровать: ЕСЛИОШИБКА(ВПР(C2; Клиенты!$A$2:$C$7; 2; ЛОЖЬ); "нет в справочнике").

Контроль после каждого ВПР: сумма по менеджерам плюс сумма «нет в справочнике» должна дать общий итог. 35 500 + 17 900 + 5 100 = 58 500 ₽ — сходится. Число строк без пары считает =СЧЁТЕСЛИ(H2:H16; "нет в справочнике") — здесь одна.

Тихая ошибка: ВПР без четвёртого аргумента

В справке Google четвёртый аргумент необязательный, и по умолчанию он равен ИСТИНА. Это режим приблизительного поиска: ВПР считает, что первый столбец отсортирован, и возвращает последнюю строку, ключ которой не больше искомого. Для шкал — скидок, комиссий, тарифов по объёму — это и нужно. Для справочника клиентов — нет.

Наш справочник отсортирован по алфавиту, поэтому для всех известных клиентов =ВПР(C2; Клиенты!$A$2:$C$7; 2) отвечает верно, и формула выглядит рабочей. Но для Новикова она не вернёт #Н/Д. «Новиков» по алфавиту стоит между «Лаптевой» и «Орловой», поэтому функция возьмёт строку Лаптевой и ответит «Сергеев». В отчёте у Сергеева станет 23 000 ₽ вместо 17 900 ₽, а строки «нет в справочнике» не будет совсем.

Если справочник не отсортирован, ответы приблизительного поиска становятся непредсказуемыми: часть ключей находится, часть получает чужие значения. Поэтому правило без исключений — ЛОЖЬ в каждом ВПР по справочнику. В XLOOKUP точное совпадение стоит по умолчанию, и эта ловушка исчезает.

Почему ВПР возвращает #Н/Д

Ключ искали, но не нашли. Причин немного, и почти все — в данных, а не в формуле. Первая — ключа действительно нет, как с Новиковым. Вторая — лишний пробел: «Ким » с пробелом в конце для ВПР другая строка. Чистят такие ключи функцией СЖПРОБЕЛЫ во вспомогательном столбце или прямо в запросе: ВПР(СЖПРОБЕЛЫ(C2); …).

Третья — разные типы. Номер заказа 1012 в одной таблице хранится числом, а в выгрузке из CRM пришёл текстом «1012». Выглядят одинаково, но ВПР числа с текстом не сравнивает и вернёт #Н/Д. Лечится приведением одной стороны: ВПР(ЗНАЧЕН(K2); …) для текста, который нужно искать среди чисел, или ВПР(ТЕКСТ(K2; "0"); …) в обратную сторону.

Четвёртая — справочник не того размера: в диапазоне A2:C7 нет новых строк, добавленных в A8. Берите справочник целыми столбцами Клиенты!$A:$C — пустые строки ВПР не мешают.

Как искать влево: массив, XLOOKUP, ИНДЕКС и ПОИСКПОЗ

ВПР возвращает значения только справа от ключевого столбца. Задача «найти дату заказа по номеру» для неё неудобна: номер в столбце B, дата левее, в A. В Google Таблицах есть три способа.

Первый — переставить столбцы прямо в формуле массивом: =ВПР(1012; {Заказы!B2:B16 \ Заказы!A2:A16}; 2; ЛОЖЬ). Фигурные скобки собирают из двух столбцов временную таблицу, где номер первый, а дата вторая. Обратная косая черта ставит столбцы рядом — так в русской локали; в локалях с десятичной точкой вместо неё пишут запятую. Ответ — 15.10.2026.

Второй — XLOOKUP: =XLOOKUP(1012; Заказы!B2:B16; Заказы!A2:A16). Где искать и что вернуть задаются отдельными диапазонами, поэтому направление не важно, а вставка столбца ничего не сдвигает. Третий — классическая связка =ИНДЕКС(Заказы!A2:A16; ПОИСКПОЗ(1012; Заказы!B2:B16; 0)). У ПОИСКПОЗ своя ловушка: по умолчанию третий аргумент равен 1 и ищет в отсортированном диапазоне, поэтому 0 обязателен.

XLOOKUP: значение по умолчанию и поиск с конца

XLOOKUP в Google Таблицах называется так же, как в английском интерфейсе, — русского имени у неё нет. Аргументов шесть: запрос, где искать, что вернуть, что показать при отсутствии, режим соответствия и режим поиска. Четвёртый аргумент заменяет обёртку ЕСЛИОШИБКА и не прячет другие ошибки: =XLOOKUP(C16; Клиенты!A2:A7; Клиенты!B2:B7; "нет в справочнике").

Режим поиска −1 ищет снизу вверх и возвращает последнее совпадение. Для аналитика это готовый ответ на вопрос «когда клиент покупал последний раз», если заказы идут по дате. У Орловой три заказа. =ВПР("Орлова"; Заказы!C2:F16; 4; ЛОЖЬ) вернёт сумму первого — 4 200 ₽. =XLOOKUP("Орлова"; Заказы!C2:C16; Заказы!F2:F16; ; 0; -1) — сумму последнего, 4 800 ₽. Дату последнего заказа, 08.10.2026, та же формула возвращает из столбца A.

XLOOKUP умеет возвращать сразу несколько столбцов: =XLOOKUP(C2; Клиенты!A2:A7; Клиенты!B2:C7) выведет менеджера и сегмент в две соседние ячейки. Сегментов два, и по оплаченным заказам B2B дал 26 600 ₽, розница — 26 800 ₽; оставшиеся 5 100 ₽ — снова Новиков.

ВПР на весь столбец через ARRAYFORMULA

Протянутая вниз формула ломается на новых строках: заказ 1016 появится в строке 17, а формулы в H17 нет. Особенно часто так бывает, когда данные приходят из Google Формы или через IMPORTRANGE. Решение — одна формула в H2 на весь столбец:

=ARRAYFORMULA(ЕСЛИ(C2:C = ""; ""; ЕСЛИОШИБКА(ВПР(C2:C; Клиенты!$A$2:$C; 2; ЛОЖЬ); "нет в справочнике")))

ВПР внутри ARRAYFORMULA принимает целый столбец запросов и возвращает столбец ответов. ЕСЛИ с пустой проверкой не даёт формуле заполнить «нет в справочнике» до конца листа. Ниже по столбцу ничего вводить нельзя: формула займёт эти ячейки, а если там уже есть данные, вернёт ошибку. Подробно об этом приёме — в статье об ARRAYFORMULA.

ВПР — это LEFT JOIN по одному ключу

Всё, что делает ВПР с ЛОЖЬ, в SQL называется LEFT JOIN: каждая строка заказов остаётся, к ней подтягиваются столбцы справочника, а если пары нет — в них пусто, как #Н/Д у Новикова. Разница в дублях. Если в справочнике Орлова записана дважды с разными менеджерами, ВПР молча возьмёт первую строку, а JOIN размножит её заказы, и выручка вырастет. Оба поведения опасны по-своему, поэтому ключ справочника проверяют на уникальность: =СЧЁТЕСЛИ(Клиенты!A:A; A2)>1 в соседнем столбце или условное форматирование с той же формулой.

Как то же соединение выглядит запросом и что происходит с дублями на настоящей базе, разобрано в статье о ВПР в Excel — там есть SQL-версия на учебной базе «Маяк». Сами соединения подробно — в статье о LEFT JOIN.

Частые вопросы

Итог

ВПР в Google Таблицах соединяет данные со справочником по ключу: клиент → менеджер, заказ → дата, канал → расход. Пишите ЛОЖЬ четвёртым аргументом, закрепляйте справочник знаками доллара, не прячьте #Н/Д в пустоту и сверяйте сумму по найденным и ненайденным с общим итогом. Для поиска влево и с конца берите XLOOKUP, для целого столбца — ARRAYFORMULA. Когда справочников становится несколько, а строк — десятки тысяч, то же соединение пишут в SQL: JOIN разбирается в курсе «SQL с нуля», первые 10 уроков бесплатно.