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

Содержание

В Функция ВПР одна из самых популярных функций в Excel. Эта страница содержит множество простых примеров ВПР.

Полное совпадение

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

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

2. Значение 4 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из четвертого столбца красной таблицы.

Примечание: логическое значение ЛОЖЬ (четвертый аргумент) сообщает функции ВПР, что необходимо вернуть точное совпадение. Если функция ВПР не может найти значение 53 в первом столбце, она вернет ошибку # Н / Д.

3. Еще один пример. Вместо того, чтобы возвращать зарплату, приведенная ниже функция ВПР возвращает фамилию (для третьего аргумента установлено значение 3) идентификатора 79.

Приблизительное совпадение

Давайте посмотрим на пример функции ВПР в режиме приблизительного совпадения (четвертый аргумент установлен в ИСТИНА).

1. Функция ВПР ниже ищет значение 85 (первый аргумент) в крайнем левом столбце красной таблицы (второй аргумент). Есть только одна проблема. В первом столбце нет значения 85.

2. К счастью, логическое значение ИСТИНА (четвертый аргумент) сообщает функции ВПР о возвращении приблизительного совпадения. Если функция ВПР не может найти значение 85 в первом столбце, она вернет наибольшее значение, меньшее 85. В этом примере это будет значение 80.

3. Значение 2 (третий аргумент) указывает функции ВПР вернуть значение в той же строке из второго столбца красной таблицы.

Примечание: всегда сортируйте крайний левый столбец красной таблицы в порядке возрастания, если вы используете функцию ВПР в режиме приблизительного совпадения (четвертый аргумент имеет значение ИСТИНА).

Vlookup выглядит правильно

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

1. Например, функция ВПР ниже ищет имя и возвращает фамилию.

2. Если вы измените номер индекса столбца (третий аргумент) на 3, функция ВПР найдет имя и вернет зарплату.

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

Первый матч

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

Объяснение: функция ВПР возвращает зарплату Миа Кларк, а не Миа Рид.

Vlookup нечувствителен к регистру

Функция ВПР в Excel выполняет поиск без учета регистра. Например, функция ВПР ниже ищет MIA (ячейка G2) в крайнем левом столбце таблицы.

Объяснение: функция ВПР не чувствительна к регистру, поэтому она ищет MIA или Mia, или mia, или miA и т. Д. В результате функция VLOOKUP возвращает зарплату Миа Кларк (первый экземпляр). Используйте ИНДЕКС, ПОИСКПОЗ и ТОЧНО в Excel для поиска с учетом регистра.

Множественные критерии

Вы хотите найти значение на основе нескольких критериев? Используйте ИНДЕКС и ПОИСКПОЗ в Excel, чтобы выполнить поиск по двум столбцам.

Примечание: приведенная выше формула массива ищет зарплату Джеймса Кларка, а не Джеймса Смита или Джеймса Андерсона.

# Н / Д ошибка

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

1. Например, функция ВПР ниже не может найти значение 28 в крайнем левом столбце.

2. Если хотите, вы можете использовать функцию IFNA, чтобы заменить ошибку # N / A понятным сообщением.

Примечание. Функция IFNA была введена в Excel 2013. Если вы используете Excel 2010 или Excel 2007, просто замените IFNA на ЕСЛИОШИБКА. Помните, что функция ЕСЛИОШИБКА также отлавливает другие ошибки. Например, #NAME? ошибка, если вы случайно неправильно написали слово ВПР.

Несколько таблиц поиска

При использовании функции ВПР в Excel у вас может быть несколько таблиц поиска. Вы можете использовать функцию ЕСЛИ, чтобы проверить, выполняется ли условие, и вернуть одну таблицу поиска, если ИСТИНА, и другую таблицу поиска, если ЛОЖЬ.

1. Создайте два именованных диапазона: Таблица1 и Таблица2.

2. Выберите ячейку E4 и войдите в функцию ВПР, показанную ниже.

Пояснение: бонус зависит от рынка (Великобритания или США) и суммы продаж. Второй аргумент функции ВПР делает свое дело. Если UK, функция VLOOKUP использует Table1, если USA, функция VLOOKUP использует Table2. Установите для четвертого аргумента функции ВПР значение ИСТИНА, чтобы получить приблизительное совпадение.

3. Нажмите Enter.

4. Выберите ячейку E4, щелкните в правом нижнем углу ячейки E4 и перетащите ее в ячейку E10.

Примечание: например, Уокер получает бонус в размере 1500 долларов. Поскольку мы используем именованные диапазоны, мы можем легко скопировать эту функцию ВПР в другие ячейки, не беспокоясь о ссылках на ячейки.

Индекс и соответствие

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