Как сделать ВПР и подтянуть данные из другой таблицы

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

Базовая формула

Ищем артикул из ячейки A2 в прайсе на отдельном листе, где артикулы в столбце A, а цены в B. Число 2 — это номер столбца в выбранном диапазоне, из которого берём результат.

=ВПР(A2;Прайс!A:B;2;0)
English: =VLOOKUP(A2,Прайс!A:B,2,0)

Последний аргумент 0 (или ЛОЖЬ) означает точное совпадение. Без него ВПР ищет приблизительно и выдаёт неверные цены.

Чтобы вместо #Н/Д было понятное слово

Когда артикула нет в прайсе, ВПР возвращает ошибку и таблица выглядит грязно. Оберните формулу в ЕСЛИОШИБКА.

=ЕСЛИОШИБКА(ВПР(A2;Прайс!A:B;2;0);"не найден")
English: =IFERROR(VLOOKUP(A2,Прайс!A:B,2,0),"не найден")

Поиск влево — ИНДЕКС и ПОИСКПОЗ

Главное ограничение ВПР: искомое значение должно быть в первом столбце диапазона, вернуть данные левее нельзя. Связка ИНДЕКС и ПОИСКПОЗ такого ограничения не имеет и работает во всех версиях.

=ИНДЕКС(A:A;ПОИСКПОЗ(E2;B:B;0))
English: =INDEX(A:A,MATCH(E2,B:B,0))

ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт из неё значение любого столбца.

Современная замена — ПРОСМОТРX

В Excel 2021 и 365 появилась ПРОСМОТРX: ищет в любую сторону и сразу принимает значение на случай, когда ничего не нашлось.

=ПРОСМОТРX(A2;Прайс!A:A;Прайс!B:B;"не найден")
English: =XLOOKUP(A2,Прайс!A:A,Прайс!B:B,"не найден")

В Excel 2016 и старше этой функции нет — используйте ВПР или ИНДЕКС с ПОИСКПОЗ.

Как это выглядит на данных

A (Артикул)B (Цена — формула)
TB-1021 250
KR-77890
XX-01не найден

Цена подтягивается из прайса автоматически по артикулу.

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

ВПР возвращает #Н/Д, хотя значение точно есть

Чаще всего мешают лишние пробелы или разный тип данных: в одной таблице артикул записан числом, в другой текстом. Примените СЖПРОБЕЛЫ и приведите оба столбца к одному формату.

При копировании формулы вниз результаты сбиваются

Диапазон поиска съезжает. Закрепите его знаками доллара — Прайс!$A$2:$B$500 — или используйте ссылку на целые столбцы Прайс!A:B.

Возвращается не тот столбец

Номер столбца считается от начала выбранного диапазона, а не от начала листа. Если диапазон начинается со столбца C, то C — это 1, D — это 2.

Вопросы и ответы

Можно ли делать ВПР по двум условиям сразу?

Напрямую нет. Либо создайте вспомогательный столбец, склеив ключи (A2&B2), либо используйте формулу ИНДЕКС с ПОИСКПОЗ по произведению условий.

Работает ли ВПР между разными файлами?

Да, но при закрытом файле-источнике формула тянет данные из кэша и требует полного пути. Надёжнее держать обе таблицы в одной книге на разных листах.

Ваша задача отличается?

Опишите её словами — ExelGen соберёт формулу под ваши столбцы и версию Excel.

Сгенерировать формулу

Читайте также