ВПР в Excel: функция с примерами

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

ВПР ищет значение в первом столбце диапазона и возвращает значение из той же строки в указанном столбце. Синтаксис по справке Microsoft:

Четыре аргумента ВПР

Пример: цена по артикулу

Прайс — в столбцах A:C (артикул, товар, цена), заказ — в столбце E. Формула в F5: =ВПР(E5;$A$5:$C$10;3;ЛОЖЬ) — найти артикул в первом столбце прайса и взять цену из третьего. Знаки $ закрепляют диапазон: когда формулу тянут вниз, прайс не «уезжает».

Цена и название по артикулу; при опечатке — «нет в прайсе»

Точный и интервальный поиск

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

Важно: если четвёртый аргумент не указать, Excel считает его ИСТИНА. На неотсортированном списке такая формула вернёт ответ, но не тот. Для поиска по артикулу или телефону всегда пишите ЛОЖЬ.

В примере со шкалой скидок: заказ на 8 500 ₽ — без скидки, 42 000 ₽ — 5 %, 130 000 ₽ — 10 %.

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

При точном поиске #Н/Д значит: такого значения в первом столбце нет. При интервальном — искомое меньше наименьшего значения шкалы. Частые причины, когда значение «вроде бы есть»:

Вместо #Н/Д можно выводить понятный текст: =ЕСЛИОШИБКА(ВПР(E13;$A$5:$C$10;3;ЛОЖЬ);"нет в прайсе"). Только помните, что ЕСЛИОШИБКА скрывает любые ошибки, — сначала убедитесь, что формула написана верно.

Сверка двух списков: кто уже есть в базе

Частая задача — понять, кто из новой выгрузки уже есть в CRM. Формула в F5: =ЕСЛИОШИБКА(ВПР(D5;$A$5:$B$14;2;ЛОЖЬ);"новый") — ищет телефон в списке из CRM и возвращает имя, под которым клиент там записан, или «новый».

Три из шести номеров уже есть в CRM — под другими именами

Из шести номеров новой выгрузки три уже есть в CRM — и записаны там по-другому: «Смирнов И.» и «Игорь Смирнов», «Линия, студия» и «Студия Линия». Это и есть дубли: один клиент — две записи. Сверка работает, только если телефоны записаны в одном формате, — как его выровнять, разобрано в статье «Формат номера телефона».

Внутри amoCRM такие повторы ищет виджет «Дубли контактов и компаний»: точное совпадение по телефону или e-mail, нечёткое — по похожему имени или названию с порогом схожести. Перед объединением — окно сверки: видно, какая запись останется основной; два разных телефона в одном поле останутся оба, а сделки и компания переезжают на основную запись.

Окно сверки перед объединением дублей

ИНДЕКС и ПОИСКПОЗ: когда ВПР не подходит

ВПР ищет только в первом столбце диапазона и берёт ответ правее. Если нужно найти артикул по названию товара, а артикул стоит левее, используют связку ИНДЕКС и ПОИСКПОЗ: =ИНДЕКС($A$5:$A$10;ПОИСКПОЗ(E5;$B$5:$B$10;0)). ПОИСКПОЗ находит номер строки с названием, ИНДЕКС берёт из этой строки артикул.

В новых версиях Excel есть ПРОСМОТРX — Microsoft называет её улучшенной версией ВПР: она ищет в любом направлении. Если файл открывают в разных версиях Excel, надёжнее ВПР или ИНДЕКС и ПОИСКПОЗ.

Как то же самое сделать в Google Таблицах — в статье «ВПР в Google Таблицах».

Файл с примерами ВПР

Четыре листа: цена по артикулу с защитой от ошибки, скидка по шкале, сверка двух списков по телефону и поиск влево через ИНДЕКС и ПОИСКПОЗ. Жёлтые ячейки — для ввода, серые — формулы.

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

Виджет «Дубли контактов и компаний» — 8 994 ₽ за 6 месяцев. Пробный период — 50 объединений.

Теги: #впр в excel #функция впр #впр пример #формула впр #впр н/д #сравнить два списка в excel

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