=ВПР(F2;A2:D6;3;ЛОЖЬ) ищет значение из F2 в первом столбце A2:D6 и возвращает значение из третьего столбца той же строки. ЛОЖЬ в конце означает «только точное совпадение». Выберите в F2 другой товар, и цена изменится. Это функция ВПР (по-английски VLOOKUP), в таблице ниже она записана по-английски: =VLOOKUP(F2,A2:D6,3,FALSE).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=ВПР(F2;A2:D6;3;ЛОЖЬ)Щёлкните G2, и таблица A2:D6 будет обведена. Замените 3 в формуле на 2, и G2 вернёт категорию вместо цены, потому что Category это второй столбец таблицы. Регистр при поиске не важен: pear находит Pear.
Синтаксис ВПР
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Аргумент | Что это | В примере |
|---|---|---|
lookup_value (искомое_значение) | Значение, которое нужно найти. | F2 (Pear) |
table_array (таблица) | Таблица, в которой ведётся поиск. ВПР ищет только в её первом столбце. | A2:D6 |
col_index_num (номер_столбца) | Какой столбец таблицы вернуть, считая от первого столбца таблицы (1). | 3 (Price) |
range_lookup (интервальный_просмотр) | ЛОЖЬ или 0 для точного совпадения. ИСТИНА, 1 или ничего для приблизительного. | ЛОЖЬ |
Номер столбца считается от начала таблицы, а не от столбца A листа. В таблице, которая начинается в столбце C, номер_столбца 2 означает столбец D. Число больше ширины таблицы возвращает #ССЫЛКА! (по-английски #REF!), а 0 возвращает #ЗНАЧ! (#VALUE!). Таблицы на этой странице показывают ошибки под английскими именами.
В русском Excel аргументы разделяются точкой с запятой, потому что запятая служит десятичным разделителем: =ВПР(F2;A2:D6;3;ЛОЖЬ). Формулы в таблицах на этой странице можно вводить и так, по-русски.
Номер столбца через ПОИСКПОЗ
Вписанная вручную 3 тихо ломается, когда кто-то вставляет столбец внутрь таблицы: формула продолжает возвращать третий столбец, в котором теперь лежит другое. Пусть номер столбца по заголовку найдёт ПОИСКПОЗ (по-английски MATCH). Здесь G1 это раскрывающийся список: выберите Stock или Category, и G2 последует за ним.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
=ВПР(F2;A2:D6;ПОИСКПОЗ(G1;A1:D1;0);ЛОЖЬ)ПОИСКПОЗ(G1;A1:D1;0) возвращает позицию "Price" в строке заголовков, 3, и ВПР использует её как номер столбца: 0.8 для Carrot. Это двумерный поиск: строка выбирается по товару, столбец по заголовку. Та же идея с ИНДЕКС вместо ВПР описана на странице ИНДЕКС и ПОИСКПОЗ.
Приблизительное совпадение: ВПР с ИСТИНА
С ИСТИНА в последнем аргументе ВПР не ищет равное значение. Она находит наибольшее значение, которое меньше искомого или равно ему. Это то, что нужно для интервалов: налоговые ставки, оценки, тарифы доставки, уровни комиссии. Первый столбец должен быть отсортирован от меньшего к большему.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
=ВПР(E2;$A$2:$B$5;2;ИСТИНА)4,200 у Ben нет в столбце A. Наибольшее значение, которое не больше его, это 1000, поэтому он получает 3%. 5,000 у Cara точно совпадает со строкой 5000 и получает 5%. 12,500 у Dev выше последнего интервала и получает последнюю ставку, 8%. Значение ниже первого интервала (здесь отрицательные продажи) возвращает #Н/Д (по-английски #N/A), поэтому таблица начинается с 0.
Знаки $ в $A$2:$B$5 удерживают таблицу на месте, когда F2 протягивается вниз до F5. Без них F3 искала бы в A3:B6 и пропустила бы первый интервал.
Пропустить четвёртый аргумент то же самое, что указать ИСТИНА. В неотсортированном списке товаров это тихая ошибка: Excel ищет так, будто список отсортирован, и может вернуть цену из чужой строки или #Н/Д для значения, которое в списке есть. Когда ищете имена, коды или идентификаторы, всегда заканчивайте формулу на ЛОЖЬ.
Почему ВПР возвращает #Н/Д
#Н/Д означает «не найдено» (таблица здесь показывает его по-английски, #N/A). Таблица ниже показывает три частые причины, а столбец G повторяет каждый поиск, обёрнутый в ЕСНД (IFNA) и СЖПРОБЕЛЫ (TRIM).
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Искомого значения нет в диапазоне поиска.В русском Excel: =ВПР(E2;$A$2:$D$6;3;ЛОЖЬ)- Значения нет в таблице. Kiwi в A2:A6 нет. Это настоящее «не найдено», и
ЕСНД(...;"Not found")превращает его в понятный текст. Замените E2 на Apple, и оба столбца покажут цену. - Лишние пробелы. В E3 стоит
"Milk "с пробелом в конце, поэтому оно не равноMilk.СЖПРОБЕЛЫ(E3)убирает пробел, и G3 находит цену. Если пробелы стоят в таблице, очистите столбец A функцией СЖПРОБЕЛЫ один раз, а не в каждом поиске. - Значение стоит в другом столбце. Fruit есть, но в столбце B. ВПР ищет только в первом столбце таблицы, поэтому E4 не находится ни в одном из столбцов. Начните таблицу со столбца, по которому ищете, или используйте ПРОСМОТРX, где столбец поиска и столбец результата задаются отдельно.
Оборачивайте поиск в ЕСНД, а не в ЕСЛИОШИБКА (IFERROR). ЕСНД перехватывает только #Н/Д, поэтому #ССЫЛКА! от неверного номера столбца по-прежнему видна, а не прячется под «Not found».
Ещё две причины:
- Числа, сохранённые как текст. Если в столбце A коды товаров введены как текст (часто после импорта, с маленьким зелёным треугольником в углу), а в F2 число 101,
=ВПР(F2;A2:B6;2;ЛОЖЬ)возвращает #Н/Д, хотя 101 в списке есть. Преобразуйте одну сторону:=ВПР(F2&"";A2:B6;2;ЛОЖЬ)ищет текст «101», а=ВПР(ЗНАЧЕН(F2);A2:B6;2;ЛОЖЬ)ищет число, когда в F2 текст. - Приблизительное совпадение на неотсортированных данных, описанное в разделе выше.
ВПР возвращает 0 вместо пустой ячейки
Когда ячейка, на которую попадает ВПР, пустая, Excel показывает 0, а не пустую ячейку. Тогда 0 в столбце Stock читается как «нет в наличии», хотя остаток просто не внесли. Добавьте к формуле &"" или проверьте длину результата:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
В русском Excel это =ВПР(F2;A2:D6;4;ЛОЖЬ)&"" и =ЕСЛИ(ДЛСТР(ВПР(F2;A2:D6;4;ЛОЖЬ))=0;"";ВПР(F2;A2:D6;4;ЛОЖЬ)). Первая короче, но превращает каждое возвращённое число в текст, и последующая СУММ его пропустит. Вторая оставляет числа числами.
ВПР с другого листа
Напишите имя листа и ! перед таблицей. Когда собираете формулу в Excel, щёлкните ярлык другого листа и выделите диапазон: Excel сам напишет Prices!A2:B6. Здесь лист Orders ищет цены на листе Prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
=ВПР(B2;Prices!$A$2:$B$6;2;ЛОЖЬ)Откройте лист Prices и измените цену Apple: сумма заказа обновится. Две детали:
- Имя листа с пробелами нужно заключать в одинарные кавычки:
=ВПР(B2;'Price list'!$A$2:$B$6;2;ЛОЖЬ). - К таблице в другой книге добавляется имя файла в квадратных скобках,
[Prices.xlsx]Prices!$A$2:$B$6. Когда этот файл закрыт, Excel показывает в формуле его полный путь, и поиск продолжает работать по сохранённому файлу.
ВПР с подстановочными знаками (частичное совпадение)
С ЛОЖЬ искомое значение может содержать подстановочные знаки: * означает любое количество символов, а ? ровно один. "*"&E2&"*" находит первый товар, название которого содержит текст из E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
=ВПР("*"&E2&"*";A2:C6;3;ЛОЖЬ)«coffee» подходит и к Iced coffee, и к Coffee beans; ВПР возвращает первое совпадение сверху, $2.90. Замените E2 на bean, чтобы получить $8.50, или на juice. Чтобы искать настоящую звёздочку или вопросительный знак, поставьте перед ним тильду: "~*".
ВПР влево
ВПР не может вернуть столбец левее того, по которому ищет: номер_столбца отсчитывается только вправо, а отрицательные числа дают ошибку. Чтобы найти товар по цене, ищите в столбце C и возвращайте столбец A с помощью ПРОСМОТРX (XLOOKUP) или ИНДЕКС и ПОИСКПОЗ:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
В русском Excel: =ПРОСМОТРX(2,4;C2:C6;A2:A6) (Excel 2021 и Microsoft 365) и =ИНДЕКС(A2:A6;ПОИСКПОЗ(2,4;C2:C6;0)) (любая версия). Обе возвращают Bread на данных первой таблицы. Подробно ПРОСМОТРX разобрана на своей странице.
Практика: стоимость доставки по весу
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
Ваша очередь: Каждая стоимость действует от своего веса до следующего веса в списке. В E2 с помощью ВПР верните стоимость доставки для веса посылки из D2.
ВПР по двум условиям
ВПР принимает одно искомое значение. Чтобы искать по двум столбцам, сделайте вспомогательный столбец, который их объединяет, поставьте его первым в таблице и ищите такой же объединённый текст. Столбец A ниже это =B2&"-"&C2, протянутая вниз, поэтому в нём Coffee-Small, Coffee-Large и так далее.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
Ваша очередь: Столбец A объединяет товар и размер через дефис. В G2 верните цену для товара из E2 и размера из F2.
Разделитель важен: "Tea"&"Large" даёт TeaLarge, а это не совпадает ни с чем в столбце A. В Excel 2021 и Microsoft 365 можно обойтись без вспомогательного столбца: =ПРОСМОТРX(1;(B2:B6=E2)*(C2:C6=F2);D2:D6); на странице поиск по нескольким условиям показан этот вариант и вариант с ИНДЕКС и ПОИСКПОЗ.
Часто задаваемые вопросы
Как сделать ВПР в Excel?
Введите =ВПР( и укажите четыре аргумента: искомое значение, таблицу (её первый столбец должен содержать это значение), номер столбца, который нужно вернуть, и ЛОЖЬ для точного совпадения. =ВПР("Pear";A2:D6;3;ЛОЖЬ) находит Pear в столбце A и возвращает значение из столбца C этой строки.
Что означает ИСТИНА или ЛОЖЬ в конце ВПР?
ЛОЖЬ (или 0) требует точного совпадения и возвращает #Н/Д, когда значения нет. ИСТИНА (или 1, или пропущенный аргумент) требует приблизительного совпадения: наибольшего значения, которое меньше искомого или равно ему. Это работает, только если первый столбец отсортирован по возрастанию.
Почему ВПР возвращает #Н/Д?
Значение не найдено в первом столбце таблицы. Обычные причины: опечатка, лишний пробел ("Milk " не равно "Milk"), число, сохранённое как текст только с одной стороны, или значение, которое стоит в другом столбце. Оберните формулу в ЕСНД, чтобы показать свой текст: =ЕСНД(ВПР(F2;A2:D6;3;ЛОЖЬ);"Not found").
Может ли ВПР искать влево?
Нет. ВПР возвращает только столбцы справа от первого столбца таблицы. Используйте =ПРОСМОТРX(F2;C2:C6;A2:A6) в Excel 2021 или Microsoft 365 или =ИНДЕКС(A2:A6;ПОИСКПОЗ(F2;C2:C6;0)) в любой версии.
Как сделать ВПР с другого листа?
Поставьте имя листа и восклицательный знак перед диапазоном: =ВПР(B2;Prices!$A$2:$B$6;2;ЛОЖЬ). Если в имени листа есть пробел, заключите его в одинарные кавычки: 'Price list'!$A$2:$B$6.
