Как использовать функцию VLOOKUP в Excel

Хотя это и не всегда требуется, обычно лучше всего отсортировать диапазон данных, которые VLOOKUP выполняет в порядке возрастания, используя первый столбец диапазона для ключа сортировки.

Если данные не отсортированы, VLOOKUP может вернуть неверный результат.

Точные и приблизительные соответствия

VLOOKUP можно настроить так, чтобы он возвращал только информацию, которая точно соответствует Поиск _value или он может быть установлен для возврата приблизительных совпадений

Определяющим фактором является диапазон_просмотра аргумент:

• установленный в FALSE, он возвращает только информацию, связанную с точными совпадениями с Поиск _value
• установленный в TRUE или опущенный, возвращает точную или приблизительную информацию, связанную с Поиск _value

В приведенном выше примере диапазон_просмотра установлено значение FALSE, поэтому VLOOKUP должно найти точное соответствие для термина Виджеты в таблице таблицы данных, чтобы вернуть цену единицы товара. Если точное совпадение не найдено, функция # N / A возвращает функцию.

Заметка: VLOOKUP не чувствителен к регистру - оба Виджеты а также виджеты являются допустимыми написаниями для приведенного выше примера.

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

Ввод аргументов функции VLOOKUP в Excel с помощью указания

В первом примере, приведенном выше, следующая формула, содержащая функцию VLOOKUP, используется для определения цены единицы для Виджеты расположенных в таблице данных.

= ВПР (А2, $ $ 5: $ B $ 8,2, ЛОЖЬ)

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

• Использование диалогового окна часто облегчает ввод аргументов функции правильно и устраняет необходимость ввода разделителей запятой между аргументами.

Следующие шаги были использованы для ввода функции VLOOKUP в ячейку B2 с помощью диалогового окна функции.

Открытие диалогового окна VLOOKUP

1. Нажмите ячейку B2, чтобы сделать ее активной ячейкой - местом, где отображаются результаты функции VLOOKUP
2. Нажми на Формулы Вкладка.
3. выберите Поиск и ссылки из ленты, чтобы открыть раскрывающийся список функций
4. Нажмите на ВПР в списке, чтобы открыть диалоговое окно функции

Данные, введенные в четыре пустые строки диалогового окна, образуют аргументы для функции VLOOKUP.

Указание на ссылки на ячейки

Аргументы для функции VLOOKUP вводятся в отдельные строки диалогового окна, как показано на изображении выше.

Ссылки на ячейки, которые будут использоваться в качестве аргументов, могут быть введены в правильную строку или, как это сделано на шагах ниже, с помощью точки и щелчка - что включает выделение требуемого диапазона ячеек с помощью указателя мыши - можно использовать для ввода их в в диалоговом окне.

Использование ссылок с относительной и абсолютной ячейкой с аргументами

Нередко использовать несколько копий VLOOKUP для возврата различной информации из одной и той же таблицы.

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

На изображении выше знаки доллара ( $ ) окружают ссылки на ячейки для таблица_массив аргумент, указывающий, что они являются абсолютными ссылками на ячейки, что означает, что они не изменятся, если функция скопирована в другую ячейку.

Это желательно, поскольку несколько копий VLOOKUP будут ссылаться на одну и ту же таблицу данных как на источник информации.

Ссылка на ячейку, используемую для lookup_value - A2 - с другой стороны , не окружен знаками доллара, что делает его относительной ссылкой на ячейку. Относительные ссылки на ячейки изменяются, когда они копируются, чтобы отражать их новое местоположение относительно позиции данных, на которые они ссылаются.

Относительные ссылки на ячейки позволяют искать несколько элементов в одной и той же таблице данных, копируя VLOOKUP в несколько местоположений и вводя различные lookup_values .

Ввод аргументов функции

1. Нажми на Поиск _value линии в ВПР диалоговое окно
2. Нажмите на ячейку A2 на листе, чтобы ввести эту ссылку на ячейку в качестве SEARCH_KEY аргумент
3. Нажми на таблица_массив строка диалогового окна
4. Выделите ячейки A5-B8 на листе, чтобы ввести этот диапазон в качестве таблица_массив аргумент - заголовки таблицы не включены
5. нажмите F4 на клавиатуре, чтобы изменить диапазон на абсолютные ссылки на ячейки
6. Нажми на Col_Index_Num строка диалогового окна
7. Наберите "А 2 на этой линии, как Col_Index_Num аргумент, поскольку ставки дисконтирования находятся в столбце 2 таблица_массив аргумент
8. Нажми на диапазон_просмотра строка диалогового окна
9. Введите слово Ложь как диапазон_просмотра аргумент
10. нажмите Войти на клавиатуре, чтобы закрыть диалоговое окно и вернуться на рабочий лист
11. Ответ: $ 14.76 - цена за единицу для виджета - должна появиться в ячейке B2 рабочего листа
12. Когда вы нажимаете на ячейку B2, полная функция = ВПР (А2, $ $ 5: $ B $ 8,2, ЛОЖЬ) появляется в строке формул над листом

Сообщения об ошибках Excel VLOOKUP

Следующие сообщения об ошибках связаны с VLOOKUP:

Ошибка A # N / A («значение недоступна»), если:

• Поиск _value не найден в первом столбце спектр аргумент
• таблица_массив аргумент неточный. Например, аргумент может содержать пустые столбцы в левой части диапазона
• диапазон_просмотра аргумент имеет значение FALSE и точное соответствие для SEARCH_KEY аргумент не может быть найден в первом столбце спектр
• диапазон_просмотра аргумент имеет значение TRUE и все значения в первом столбце спектр больше, чем SEARCH_KEY

#REF! ошибка отображается, если:

• Col_Index_Num аргумент больше числа столбцов в массиве таблицы.