Функция ВПР в Excel
Ищет значение по ключу в одной таблице и подтягивает данные из другой. Восемь рабочих примеров, разбор ошибки #Н/Д и готовый файл-сборник.
Коротко
ВПР ищет значение в первом столбце справочника и возвращает данные из той же строки, но из нужного столбца. Так по артикулу подтягивают цену, по табельному номеру — оклад, по номеру заказа — статус: когда данные лежат в двух таблицах и их надо свести по общему ключу.
Синтаксис: =ВПР(что ищем; где ищем; номер столбца; тип совпадения). Последний аргумент ЛОЖЬ (или 0) — точное совпадение, его ставят почти всегда; ИСТИНА (или 1) — приблизительное, только для отсортированных по возрастанию диапазонов вроде шкалы ставок.
В примерах ниже справочник товаров лежит в B7:E12 — столбцы «Артикул», «Товар», «Цена», «Остаток», — а искомый артикул вводится в H5. У артикула TK-1045 две строки: это два поступления одного товара на склад, на них видно разницу между ВПР и СУММЕСЛИ. Восьмой пример работает по отдельной шкале категорий в G9:H11. Всё это лежит в файле-сборнике — откроете и увидите формулы на живых данных.
То же самое формулой в Excel
=ВПР($H$5;$B$7:$E$12;3;ЛОЖЬ) — цена по артикулу; ЛОЖЬ — точное совпадение, без неё Excel молча найдёт не то=ЕСЛИОШИБКА(ВПР($H$5;$B$7:$E$12;3;ЛОЖЬ);"не найдено") — та же формула, но #Н/Д превращается в понятный текст=ВПР($H$5;$B$7:$E$12;4;ЛОЖЬ) — остаток по тому же артикулу — меняется только номер столбца=ВПР($H$5;$B$7:$E$12;ПОИСКПОЗ("Цена";$B$6:$E$6;0);ЛОЖЬ) — номер столбца ищет ПОИСКПОЗ по заголовку — формула не ломается при вставке столбца=ИНДЕКС($B$7:$B$12;ПОИСКПОЗ("Полка Nord";$C$7:$C$12;0)) — поиск влево: ВПР так не умеет, а ИНДЕКС с ПОИСКПОЗ умеют=СУММЕСЛИ($B$7:$B$12;$H$5;$E$7:$E$12) — у TK-1045 две строки склада: ВПР вернёт остаток первой, СУММЕСЛИ сложит обе=ЕСЛИ(СЧЁТЕСЛИ($B$7:$B$12;$H$5)>0;"есть";"нет") — просто проверить, есть ли артикул в справочнике, без ВПР вообще=ВПР(20000;$G$9:$H$11;2;ИСТИНА) — приблизительный поиск по шкале категорий: 20 000 попадает в «средний»Сборник ВПР: восемь примеров на одном листе
Восемь формул на готовых данных: точный поиск, защита от #Н/Д, номер столбца через ПОИСКПОЗ, поиск влево связкой ИНДЕКС и ПОИСКПОЗ, сумма остатков и приблизительный поиск по шкале. Меняете артикул в жёлтой ячейке — все примеры пересчитываются.
Файл
vpr-sbornik.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Встаньте в пустую ячейку и начните с
=ВПР(. - Что ищем. Кликните ячейку с ключом — артикулом, номером, именем. В примере это
H5. Поставьте точку с запятой. - Где ищем. Выделите справочник так, чтобы ключ оказался в его первом столбце, а нужные данные — правее. В примере
B7:E12. Нажмите F4 — диапазон закрепится знаком$и не сползёт, когда протянете формулу вниз. - Номер столбца. Посчитайте, каким по счёту в выделенном диапазоне идёт нужный столбец: цена — третий, значит
3. Счёт от левого края диапазона, а не от края листа. - Тип совпадения.
ЛОЖЬили0— точное, ставьте его.ИСТИНАили1— приблизительное, только для сортированных диапазонов. - Закройте скобку, Enter. Потяните за уголок ячейки вниз — формула размножится на весь столбец.
Где обычно ломается
Ошибка #Н/Д значит «не нашёл». Пять причин по частоте:
- Ключ не в первом столбце диапазона. ВПР смотрит только в самый левый столбец выделения. Если искомое правее — оно невидимо: переставьте столбцы или возьмите ИНДЕКС с ПОИСКПОЗ (пример выше).
- Лишние пробелы. «TK-1043 » с пробелом на конце и «TK-1043» — для ВПР разные значения. Прогоните ключ через
СЖПРОБЕЛЫ. - Число против текста. Артикул в одной таблице — число, в другой — текст (зелёный уголок в углу ячейки). Совпадения не будет; приведите обе колонки к одному типу.
- Диапазон не закреплён. Формулу протянули вниз, а
B7:E12сполз наB8:E13— часть строк ищет мимо справочника. Закрепите через$(F4). - Приблизительный поиск по несортированным данным. С
ИСТИНАпервый столбец обязан идти по возрастанию, иначе ВПР вернёт неверное — и без всякой ошибки, что хуже #Н/Д. Для точного поиска всегдаЛОЖЬ.