Что получится и какие данные нужны
Чтобы понять, как работает ВПР, найдём цену по коду товара. Функция ищет код в первом столбце выбранного диапазона и возвращает значение из той же строки. В нашем примере для P-103 получится 350 рублей.
Создайте на одном листе таблицу A1:C6. Заголовки A1, B1 и C1: «Код», «Товар», «Цена, руб.». Ниже внесите пять строк:
- P-101, Блокнот, 120;
- P-102, Папка, 240;
- P-103, Степлер, 350;
- P-104, Дырокол, 480;
- P-105, Лампа, 990.
Это условный прайс. Коды вводите одинаково, без пробелов по краям; цены сохраните числами. Столбец D оставьте пустым. В E1 напишите «Искомый код», в F1 «Результат ВПР». В E2 введите P-103. Ячейка F2 предназначена для формулы.
Как ввести ВПР и разобрать аргументы
- Выберите F2.
- Вставьте формулу целиком:
=ВПР(E2;$A$2:$C$6;3;ЛОЖЬ)- Нажмите Enter. Ожидаемая цена для P-103: 350 рублей.
У ВПР в Excel четыре аргумента. E2 задаёт код для поиска. $A$2:$C$6 ограничивает таблицу пятью строками без заголовков. Код должен находиться в её первом столбце, здесь это A. Число 3 указывает столбец цены внутри выбранного диапазона: A считается первым, B вторым, C третьим.
ЛОЖЬ включает точное совпадение. Если код отсутствует, функция вернёт ошибку. Пропущенный последний аргумент означает приблизительный поиск, для которого требуется сортировка первого столбца. Для поиска по коду явно оставьте ЛОЖЬ.
Проверьте копирование формулы: введите P-105 в E3 и P-999 в E4, затем скопируйте F2 в F3:F4. Ссылки на коды сменятся на E3 и E4. Знаки $ удержат диапазон прайса на месте. Без них при копировании вниз диапазон тоже сместится, и часть товаров выпадет из поиска.
Как проверить результат
Сверьте F2:F4 с исходным прайсом. Для P-103 цена равна 350 рублям, для P-105 она равна 990 рублям. Кода P-999 в A2:A6 нет, поэтому точный поиск должен вернуть #Н/Д или #N/A, в зависимости от программы и языка.
Так видно, как работает ВПР и при найденном коде, и при отсутствии совпадения. Если вместо ошибки появилась цена другого товара, сначала проверьте последний аргумент. Затем сравните формулу с образцом и убедитесь, что диапазон остался $A$2:$C$6. Проверьте ссылку в F2 и во всех скопированных ячейках.
Что означают ошибки ВПР
#Н/Д / #N/A: точного совпадения нет. Проверьте наличие кода в первом столбце диапазона и лишние пробелы. Если ищете числовой код, число и текст с такими же цифрами могут мешать сопоставлению. В нашем прайсе коды текстовые и содержат букву P.
#ССЫЛКА! / #REF!: номер возвращаемого столбца выходит за границы диапазона. В A2:C6 всего три столбца, поэтому номер 4 для него неверен. Столбцы считаются от левого края выбранного диапазона.
Если функция вернула чужую цену, проверьте режим точного совпадения и адреса. При повторяющихся кодах ВПР возвращает первое найденное совпадение. Когда один код относится к нескольким товарам, сначала исправьте справочник, иначе формула может выдать формально верный, но неподходящий результат.
Excel, Google Таблицы и частые вопросы
Пример выше использует русские имена функций и точку с запятой. Для VLOOKUP в Google Таблицах при английских именах функций запись будет такой:
=VLOOKUP(E2;$A$2:$C$6;3;FALSE)Разделитель аргументов зависит от региональных настроек. Если ваша программа требует запятые, замените ими точки с запятой.
Можно искать слева от кода? ВПР возвращает значение из указанного столбца выбранного диапазона. Для этого способа столбец кода должен быть первым, цена справа от него.
Нужно сортировать прайс?
При ЛОЖЬ сортировка не нужна.
Что делать, если кода нет?
Сначала проверьте исходный список и написание кода. Ошибка #Н/Д для P-999 ожидаема: в нашем примере такого товара нет.
Повторите поиск на своём прайсе и вручную сверьте одну найденную цену с исходной строкой.
Источники: Microsoft Support, справка Google.
