ВПР в Google Таблицах

Что делает ВПР

ВПР ищет значение в первом столбце выбранного диапазона и возвращает значение из столбца с указанным номером в той же строке. Например: найти ID клиента в справочнике и вернуть его название из второго столбца или менеджера из третьего.

Синтаксис

=ВПР(запрос; диапазон; индекс; [отсортировано])

Так функцию описывает справка Google Таблиц. Если совпадений несколько, ВПР вернёт первое.

Пример: клиент и менеджер по ID

На листе «Клиенты» — справочник: ID, название, менеджер, город. На листе «Сделки» — номер сделки, ID клиента и сумма. В столбец «Клиент» на листе сделок пишем:

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

Для менеджера — тот же ВПР с индексом 3. В примере из файла по шести сделкам из семи подтянулись клиенты и менеджеры, а сделка с ID K-110, которого нет в справочнике, получила пометку «нет в справочнике» — сразу видно, что справочник нужно дополнить.

Почему ЛОЖЬ обязателен

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

Ошибка #Н/Д и как её убрать

#Н/Д значит, что значение не найдено. Вместо ошибки можно вывести понятный текст: =ЕСНД(ВПР(B5;Клиенты!A5:D40;2;ЛОЖЬ);"не найдено"). ЕСЛИОШИБКА заменяет любую ошибку, ЕСНД — только #Н/Д.

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

ВПР по двум условиям

Сразу по двум условиям ВПР не ищет. Решение из справки Google — вспомогательный столбец, который склеивает условия знаком «&»: =B5&C5 — «Дмитрийноябрь». ВПР ищет по склеенному ключу: =ВПР(F5&G5;$A$5:$D$40;4;ЛОЖЬ). В примере так подтягивается план менеджера на нужный месяц.

ВПР из другой таблицы

ВПР работает с диапазоном внутри файла — с этого же или другого листа: 'Клиенты'!$A$5:$D$40. Если справочник лежит в другом файле, данные сначала подтягивают функцией IMPORTRANGE — как она работает, разобрано в статье «IMPORTRANGE в Google Таблицах».

Частые ошибки

• Нет ЛОЖЬ. Чужие данные вместо нужных — без сообщения об ошибке.

• Искомый столбец не первый. ВПР ищет только по первому столбцу диапазона: начните диапазон с него.

• Индекс больше числа столбцов. Номер столбца не может выходить за диапазон — формула вернёт ошибку.

• Диапазон без $. При протягивании он сдвигается, и нижние строки ищут не во всём справочнике.

• Повторы в ключе. ВПР возвращает первое совпадение — дубли в справочнике дают неверные данные.

Файл с примерами

Пять листов: «Клиенты» — справочник; «Сделки» — клиент и менеджер по ID с пометкой «нет в справочнике» и суммой по менеджеру; «Два условия» — поиск по менеджеру и месяцу через вспомогательный столбец; «Ошибки» — почему ВПР не находит значение; «Как пользоваться». Файл открывается в Excel и в Google Таблицах.

Скачать файл с примерами ВПР · xlsx, 13 КБ

Данные из amoCRM — в таблицу сами

Часто ВПР нужен, чтобы подтянуть в отчёт данные из CRM: суммы сделок, этапы, ответственных. Чтобы не копировать выгрузку руками, виджет «Экспорт в Google Таблицы» выгружает сделки, контакты и компании из amoCRM в Google Таблицу и обновляет строку сам после изменения в amoCRM. Рядом вы держите свои колонки с ВПР, формулами и сводными таблицами — виджет пишет только в свои колонки и ваши не трогает. 1 998 ₽ в месяц за аккаунт; пробный период — 14 дней или 70 строк.

Теги: #впр в гугл таблицах #функция впр в гугл таблицах #формула впр #впр из другой таблицы #vlookup google sheets #Экспорт в Google Таблицы

Converssion CRM — официальный партнёр amoCRM с 2017 года. Внедряем и сопровождаем amoCRM, делаем собственные виджеты для амоМаркета, чат-ботов, ИИ-агентов, сайты и рекламу. Услуги · Кейсы клиентов · Виджеты