Как использовать функцию ВПР в Excel?

Excel VLOOKUP функция

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

В этом руководстве рассказывается о том, как использовать функцию ВПР, с объяснением синтаксис, Аргументы, возвращаемое значение, функциональные примечания и простые примеры в деталях.
Кроме того, в конце урока мы перечисляем множество практических примеров часто используемых формул ВПР, в том числе Vlookup на разных листах или книгах, Vlookup и сохраните форматирование ячейки результата, vlookup возвращает несколько результатов и другие формулы vlookup в общих случаях.

Синтаксис

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

аргументы

Lookup_value (обязательный): Значение, которое вы ищете. Он должен быть в первом столбце диапазона table_array.

Table_array (обязательный): Таблица содержит два или более столбца, в которых находятся столбец значения поиска и столбец значения результата.

Col_index (обязательный): Конкретный номер столбца (это целое число) table_array, из которого вы вернете совпадающее значение.

Номер первого столбца равен 1 для самого левого столбца table_array.

Range_lookup (необязательный): Это логическое значение, которое определяет, будет ли эта функция ВПР возвращать точное или приблизительное совпадение:

Если же линия индикатора range_lookup либо ИСТИНА или опущен, функция ВПР вернет точное или приблизительное совпадение. Если точное совпадение не может быть найдено, оно будет соответствовать следующему наименьшему значению.
=VLOOKUP(lookup_value, table_array, col_index, TRUE)
=VLOOKUP(lookup_value, table_array, col_index)

Важнo: В этом случае значения в первом столбце таблица_массив необходимо отсортировать по возрастанию;
В противном случае функция VLOOKUP может не вернуть правильное значение.

Если же линия индикатора range_lookup либо НЕПРАВДА или 0, функция ВПР вернет только точное совпадение. Если точное совпадение не может быть найдено, будет возвращено значение ошибки # Н / Д. Но если в первом столбце таблицы есть два или более значений таблица_массив что соответствует искомое_значение, vlookup будет использовать только значение первого найденного.
=VLOOKUP(lookup_value, table_array, col_index, FALSE)
=VLOOKUP(lookup_value, table_array, col_index, 0)

В этом случае значения в первом столбце таблицы таблица_массив не нужно сортировать.

Возвращаемое значение

Функция ВПР вернет совпадающее значение из таблицы на основе значения поиска в первом столбце.

Примечания к функциям

1. Функция ПРОСМОТР ищет значение только слева направо.
Значение поиска находится в крайнем левом столбце, а значение результата должно быть в любом столбце справа от значения поиска.
2. Есть два режима согласования для функции ВПР. Они точно совпадают и примерно совпадают.
Функция ВПР по умолчанию будет использовать приблизительное совпадение.
3. Он вернет значение ошибки # Н / Д, если искомое значение не может быть найдено.

Примеры

В этом разделе приведены примеры, показывающие, как использовать функцию VOOKUP в Excel.

Пример 1: точное совпадение VS приблизительное совпадение с функцией ВПР

1.1 Vlookup точное совпадение:

Как показано на приведенном ниже снимке экрана, как найти значение точного совпадения «Peach» и вернуть его соответствующее значение из третьего столбца в диапазоне таблицы B3: E8? Для этого вы можете сделать следующее.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.
=VLOOKUP(G4,B3:E8,3,FALSE)

1.2 Примерное совпадение Vlookup:

Как показано на скриншоте ниже, вы хотите найти номер заказа «10006», если номер не может быть найден, выполните приблизительное совпадение.

1. Выберите пустую ячейку, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.
=VLOOKUP(H4,B3:F8,3,TRUE)

Заметки:

В двух приведенных выше формулах G4 и H4 ссылки на ячейки, содержащие искомые значения;
B3: E8 и B3: F8 - это диапазоны таблицы, в которых находятся столбец значения поиска и столбец значения результата;
3 номер столбца для возврата совпадающего значения;
НЕПРАВДА указывает на нахождение значения точного совпадения в этом случае;
ИСТИНА преобразует приблизительное совпадение. Поскольку искомое значение 10006 не было найдено в первом столбце, оно будет соответствовать следующему наименьшему значению 10005 и вернет результат 9.00 долларов США из третьего столбца таблицы.

Пример 2: Vlookup возвращает пустое поле или конкретное значение вместо # N / A

При выполнении точного совпадения с функцией ВПР будет возвращено значение ошибки # Н / Д, если искомое значение не может быть найдено. Смотрите скриншот:

Как оставить ячейку результата пустой или вернуть конкретное значение вместо # N / A, если искомое значение не может быть найдено? Вот решение ниже.

1. Скопируйте приведенную ниже формулу в ячейку, в которой будет выведен результат, и нажмите Enter ключ:
=IFERROR(VLOOKUP(H4,B3:F8,3,0),"Not found")

Внимание: В формуле «не найдено»- это содержимое, которое вы будете отображать в ячейке результата вместо ошибки # N / A. Чтобы ячейка результата оставалась пустой, замените текст «не найдено» пробелом. И вы можете изменить его в зависимости от ваших потребностей.

Больше примеров

Формулы Vlookup - в книгах / листах

Значения Vlookup на нескольких листах
Вы можете применить функцию vlookup, чтобы вернуть совпадающие значения в таблице рабочего листа. Однако, если вам нужно использовать значение vlookup на нескольких листах, как вы можете это сделать? Эта статья содержит подробные инструкции, которые помогут вам легко решить проблему.
Нажмите, чтобы узнать больше ...

Vlookup для сравнения двух списков на отдельных листах
Предположим, что есть два списка имен, расположенных на разных листах «Имя-1» и «Имя-2», для сравнения этих двух списков и поиска совпадающих имен в Имена-1, если они выходят в Имена-2. Формула ВПР в этой статье окажет вам услугу.
Нажмите, чтобы узнать больше ...

Vlookup к другой книге
Обычно функцию ВПР можно использовать только в одной книге. Если вы хотите применить функцию ВПР в другой книге, попробуйте формулу из этой статьи.
Нажмите, чтобы узнать больше ...

Формулы Vlookup - Сохранение форматирования результатов

Сохранять форматирование ячеек при Vlookup
Обычно, применяя формулу ВПР, вы получаете результат без какого-либо форматирования. Как сохранить исходное форматирование ячейки результата? Вот решение.
Нажмите, чтобы узнать больше ...

Vlookup и вернуть цвет фона вместе со значением поиска
Если вы хотите вернуть результат вместе с исходным цветом фона при применении формулы VLOKUP, может помочь метод, описанный в этой статье.
Нажмите, чтобы узнать больше ...

Формат даты Vlookup и возврата вместо числа
Вы можете заметить, что дата отображается как серийный номер вместо формата даты при использовании функции ВПР, а затем вам необходимо вручную отформатировать эту ячейку результата для форматирования даты. Есть ли какой-нибудь удобный способ справиться с этим, кроме ручного изменения форматирования ячеек на формат даты? Эта статья покажет вам простой способ решить эту проблему.
Нажмите, чтобы узнать больше ...

Vlookup и возврат соответствующего значения с комментарием ячейки
Когда вы применяете функцию Vlookup для возврата совпадающего значения, она вернет только значение. Иногда вам может потребоваться vlookup и вернуть совпадающее значение, включая его исходный комментарий. Как ты мог это снять?
Нажмите, чтобы узнать больше ...

Формулы Vlookup - возврат нескольких результатов

Vlookup и возврат совпадающих значений в нескольких столбцах
Обычно применение функции Vlookup может вернуть совпадающее значение только из одного столбца. Иногда вам может потребоваться извлечь совпадающие значения из нескольких столбцов на основе критериев. Вот решение для вас.
Нажмите, чтобы узнать больше ...

Vlookup возвращает несколько значений в одной ячейке
Обычно при применении функции ВПР, если есть несколько значений, соответствующих критериям, вы можете получить результат только для первого из них. Если вы хотите вернуть все совпавшие результаты и отобразить их все в одной ячейке, как этого добиться?
Нажмите, чтобы узнать больше ...

Vlookup и возврат нескольких значений без дубликатов
Иногда вам может потребоваться vlookup и сразу вернуть несколько совпадающих значений в одну ячейку. Но как сохранить уникальные значения только при появлении повторяющихся результатов? Вот решение.
Нажмите, чтобы узнать больше ...

Vlookup и возврат всей строки совпадающего значения
Обычно использование функции vlookup может возвращать результат только из определенного столбца в той же строке. Эта статья покажет вам, как вернуть всю строку данных на основе определенных критериев.
Нажмите, чтобы узнать больше ...

Формулы Vlookup - другие распространенные случаи

С легкостью используйте vlookup и суммируйте в Excel
Сочетание функций vlookup и sum помогает найти определенный критерий и одновременно суммировать все значения на основе этого критерия. Здесь мы предоставляем подробные инструкции, которые помогут вам легко найти определенное значение и суммировать все совпадения в столбцах или строках в Excel.
Нажмите, чтобы узнать больше ...

Обратный просмотр Vlookup или в обратном порядке
Как правило, функция ВПР ищет значения слева направо в таблице массивов и требует, чтобы значение поиска оставалось в левой части целевого значения. Но иногда вы можете знать целевое значение и захотеть узнать значение поиска в обратном порядке. Следовательно, вам необходимо выполнить поиск в обратном порядке в Excel. В этой статье есть несколько способов легко справиться с этой проблемой!
Нажмите, чтобы узнать больше ...

Vlookup определенное значение и возвращает только истину или ложь / да или нет
Обычно при использовании формулы Vlookup для поиска точно совпадающего значения возвращается значение ошибки # N / A, если значение не может быть найдено, или правильный результат при сопоставлении значения. Однако в некоторых случаях нам нужно только вернуть Yes или No / True или False, чтобы просто напомнить себе, существует ли значение. Решение из этой статьи может помочь.
Нажмите, чтобы узнать больше ...

Vlookup в двухмерной таблице
Иногда вам нужно vlookup вернуть относительное значение, основанное как на строке, так и на столбце, то есть вам нужно вернуть данные из двухмерной таблицы. В этом случае сочетание функций ВПР и ПОИСКПОЗ может помочь решить проблему.
Нажмите, чтобы узнать больше ...

Лучшие инструменты для работы в офисе

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее ...) и экономия 80% времени для вас.

Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.

Лента Excel (с установленным Kutools for Excel)

Читать далее Скачать

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

Одна секунда для переключения между десятками открытых документов!
Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.

Снимок экрана Excel (с установленной вкладкой Office)