Функция Excel VLOOKUP используется для поиска и возврата информации из таблицы данных на основе выбранного вами значения поиска.
Как правило, VLOOKUP требует, чтобы значение поиска находилось в крайнем левом столбце таблицы данных, а функция возвращает другое поле данных, расположенное в той же строке справа от этого значения.
Однако, комбинируя VLOOKUP с функцией CHOOSE , можно создать левую формулу поиска, которая:
- Позволяет искать значение из любого столбца в таблице данных
- Возвращает информацию, расположенную в любом столбце слева от значения поиска
Эти инструкции относятся к версиям Excel 2019, 2016, 2013, 2010 и Excel для Office 365.
Учебник. Использование функций VLOOKUP и CHOOSE в формуле левого поиска
Чтобы создать формулу поиска слева, показанную на изображении примера, используйте формулу:
= ВПР ($ D $ 2, ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
В этом примере формула позволяет найти детали, поставляемые различными компаниями, перечисленными в столбце 3 таблицы данных.
Задача функции CHOOSE в формуле состоит в том, чтобы обмануть VLOOKUP, полагая, что столбец 3 является столбцом 1. В результате имя компании можно использовать в качестве значения поиска, чтобы найти имя детали, поставляемой каждой компанией.
Введите учебные данные
Откройте диалоговое окно VLOOKUP
Хотя формулу можно ввести непосредственно в ячейку F1 на листе, многие люди испытывают трудности с синтаксисом формулы.
В этом случае безопаснее использовать диалоговое окно VLOOKUP. Почти все функции Excel имеют диалоговое окно, которое позволяет вам вводить каждый аргумент функции в отдельной строке.
Ввод аргументов в диалоговое окно VLOOKUP
Аргументы функции — это значения, используемые функцией для вычисления результата.
В диалоговом окне функции имя каждого аргумента находится в отдельной строке, за которой следует поле для ввода значения.
Введите следующие значения для каждого из аргументов VLOOKUP в правильной строке диалогового окна, как показано на прилагаемом изображении.
Значение поиска
Значение поиска — это поле информации, которое используется для поиска в массиве таблиц. VLOOKUP возвращает другое поле данных из той же строки, что и значение поиска.
В этом примере используется ссылка на ячейку для местоположения, в котором название компании будет введено в таблицу. Преимущество этого состоит в том, что это позволяет легко изменить название компании без редактирования формулы.
- Нажмите на строку lookup_value в диалоговом окне.
- Нажмите на ячейку D2, чтобы добавить эту ссылку на ячейку в строку lookup_value .
- Нажмите клавишу F4 на клавиатуре, чтобы сделать ссылку на ячейку абсолютной — $ D $ 2.
Абсолютные ссылки на ячейки используются для значений поиска и аргументов массива таблиц во избежание ошибок, если формула поиска копируется в другие ячейки на листе.
Вход в функцию ВЫБРАТЬ
Аргумент массива таблицы — это блок непрерывных данных, из которых извлекается конкретная информация.
Обычно VLOOKUP смотрит только справа от аргумента значения поиска, чтобы найти данные в массиве таблицы. Чтобы он выглядел влево, нужно обмануть VLOOKUP, переставив столбцы в массиве таблицы с помощью функции CHOOSE.
В этой формуле функция CHOOSE выполняет две задачи:
- Он создает массив таблиц шириной всего в два столбца (столбцы D и F).
- Он изменяет порядок столбцов в массиве таблиц справа налево, поэтому столбец F идет первым, а столбец D — вторым.
Ввод функций
При вводе функций вручную каждый из аргументов функции должен быть разделен запятой.
- В диалоговом окне функции VLOOKUP щелкните строку Table_array .
- Введите следующую функцию CHOOSE : CHOOSE ({1,2}, $ F: $ F, $ D: $ D)
Номер столбца
Обычно порядковый номер столбца указывает, в каком столбце массива таблицы содержатся данные, к которым вы стремитесь. Однако в этой формуле это относится к порядку столбцов, установленному функцией CHOOSE.
Функция CHOOSE создает массив таблиц шириной в два столбца, за которым сначала следует столбец F, а затем столбец D. Поскольку искомая информация — имя детали — находится в столбце D, значение аргумента индекса столбца должно быть равно 2.
- Нажмите на строку Col_index_num в диалоговом окне .
- Введите 2 в этой строке.
Диапазон поиска
Аргумент Range_lookup в VLOOKUP — это логическое значение (только TRUE или FALSE), которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение со значением поиска.
- Если TRUE или этот аргумент пропущен, VLOOKUP возвращает либо точное совпадение с Lookup_value, либо, если точное совпадение не найдено, VLOOKUP возвращает следующее наибольшее значение. Чтобы формула сделала это, данные в первом столбце Table_array должны быть отсортированы в порядке возрастания .
- Если FALSE, VLOOKUP использует только точное совпадение с Lookup_value. Если в первом столбце Table_array есть два или более значений, которые соответствуют значению поиска, используется первое найденное значение. Если точное совпадение не найдено, возвращается ошибка # N / A.
В этом уроке, так как мы ищем определенное имя детали, Range_lookup устанавливается в False, так что по формуле возвращаются только точные совпадения.
- Нажмите на строку Range_lookup в диалоговом окне.
- Введите слово False в этой строке, чтобы указать, что мы хотим, чтобы VLOOKUP возвращал точное соответствие для данных, которые мы ищем.
- Нажмите OK, чтобы заполнить формулу поиска слева и закрыть диалоговое окно.
- Поскольку мы еще не ввели название компании в ячейку D2, в ячейке E2 появляется ошибка # N / A.
Возврат данных с левой формулой поиска
Чтобы узнать, какие компании поставляют какие детали, введите название компании в ячейку D2 и нажмите клавишу ВВОД на клавиатуре.
Название детали отображается в ячейке E2.
- Нажмите на ячейку D2 в вашем рабочем листе .
- Введите Gadgets Plus в ячейку D2 и нажмите клавишу ВВОД на клавиатуре.
- Текст «Гаджеты» — часть, поставляемая компанией Gadgets Plus — должен отображаться в ячейке E2.
Далее проверьте формулу поиска, введя другие названия компаний в ячейку D2, и соответствующее имя детали должно появиться в ячейке E2.
Если в ячейке E2 появляется сообщение об ошибке, например # N / A, проверьте наличие орфографических ошибок в ячейке D2.
Создание двухколоночного массива таблиц
Синтаксис для функции ВЫБРАТЬ является:
= ВЫБРАТЬ (номер_индекса, значение1, значение2, … значение254)
Функция CHOOSE обычно возвращает одно значение из списка значений (от Value1 до Value254) на основе введенного номера индекса.
Если номер индекса равен 1, функция возвращает Value1 из списка; если номер индекса равен 2, функция возвращает Value2 из списка и так далее.
Когда вводятся несколько порядковых номеров, функция возвращает несколько значений в любом порядке. Получение CHOOSE для возврата нескольких значений выполняется путем создания массива .
Чтобы ввести массив, заключите числа, введенные в фигурные скобки или скобки. Для номера индекса вводятся два числа: {1,2} .
Следует отметить, что CHOOSE не ограничивается созданием таблицы из двух столбцов. Включая в массив дополнительный номер, например {1,2,3}, и дополнительный диапазон в аргументе значения, CHOOSE создает таблицу из трех столбцов.
Дополнительные столбцы позволяют вам возвращать различную информацию с помощью левой формулы поиска, изменяя аргумент номера индекса столбца VLOOKUP на номер столбца, содержащего нужную информацию.
Изменение порядка столбцов с помощью функции ВЫБРАТЬ
В функции CHOOSE, используемой в этой формуле:
ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D)
диапазон для столбца F указан перед столбцом D.
Поскольку функция CHOOSE устанавливает массив таблиц VLOOKUP (источник данных для этой функции), переключение порядка столбцов в функции CHOOSE передается VLOOKUP.
Что касается VLOOKUP, то массив таблиц имеет ширину всего в два столбца, столбец F слева и столбец D справа. Поскольку столбец F содержит название компании, которую мы хотим найти, а столбец D содержит имена частей, VLOOKUP может выполнять свои обычные функции поиска при поиске данных, которые расположены слева от значения поиска.
В результате VLOOKUP может использовать название компании, чтобы найти часть, которую они поставляют.