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

Новый XLOOKUP в Excel заменит VLOOKUP, предоставив мощную замену одной из самых популярных функций Excel. Эта новая функция устраняет некоторые ограничения функции ВПР и обладает дополнительными функциями. Вот что вам нужно знать.

Что такое XLOOKUP?

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

На данный момент XLOOKUP доступен только пользователям программы Insiders. Любой может присоединиться к программе Insiders, чтобы получить доступ к новейшим функциям Excel, как только они станут доступны. Вскоре Microsoft начнет развертывать его для всех пользователей Office 365.

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

Давайте перейдем непосредственно к примеру XLOOKUP в действии. Возьмите пример данных ниже. Мы хотим вернуть отдел из столбца F для каждого идентификатора в столбце A.

Это классический пример поиска точного совпадения. Для функции XLOOKUP требуется всего три фрагмента информации.

На изображении ниже показан XLOOKUP с шестью аргументами, но для точного совпадения необходимы только первые три. Итак, сосредоточимся на них:

• Lookup_value: то, что вы ищете.
• Lookup_array: Где искать.
• Return_array: диапазон, содержащий возвращаемое значение.

Для этого примера будет работать следующая формула: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Давайте теперь рассмотрим пару преимуществ XLOOKUP по сравнению с VLOOKUP.

Нет больше номера индекса столбца

Печально известный третий аргумент VLOOKUP заключался в указании номера столбца информации, возвращаемой из массива таблиц. Это больше не проблема, так как функция XLOOKUP позволяет выбрать диапазон для возврата (столбец F в этом примере).

И не забывайте, XLOOKUP может просматривать данные слева от выбранной ячейки, в отличие от VLOOKUP. Подробнее об этом ниже.

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

Точное совпадение по умолчанию

При изучении ВПР всегда было непонятно, почему нужно указывать точное совпадение.

К счастью, XLOOKUP по умолчанию использует точное совпадение — гораздо более распространенная причина использования формулы поиска). Это уменьшает необходимость отвечать на этот пятый аргумент и гарантирует меньшее количество ошибок пользователей, не знакомых с формулой.

Короче говоря, XLOOKUP задает меньше вопросов, чем VLOOKUP, более удобен для пользователя и более надежен.

XLOOKUP может смотреть налево

Возможность выбора диапазона поиска делает XLOOKUP более универсальным, чем VLOOKUP. При использовании XLOOKUP порядок столбцов таблицы не имеет значения.

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

В приведенном ниже примере нам нужно найти идентификатор (столбец E) и вернуть имя человека (столбец D).

Этого можно добиться с помощью следующей формулы: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Что делать, если не найдено

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

Поэтому пользователи быстро ищут, как скрыть эту ошибку, потому что она неверна или бесполезна. И, конечно же, есть способы сделать это.

XLOOKUP поставляется со своим собственным встроенным аргументом «если не найдено» для обработки таких ошибок. Давайте посмотрим на это в действии с предыдущим примером, но с ошибочным идентификатором.

Следующая формула будет отображать текст «Неверный идентификатор» вместо сообщения об ошибке: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Использование XLOOKUP для поиска диапазона

Хотя это и не так распространено, как точное совпадение, очень эффективное использование формулы поиска заключается в поиске значения в диапазонах. Возьмем следующий пример. Мы хотим вернуть скидку в зависимости от потраченной суммы.

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

XLOOKUP имеет необязательный пятый аргумент (помните, что по умолчанию используется точное совпадение) с именем режим совпадения.

Вы можете видеть, что XLOOKUP имеет больше возможностей с приблизительными совпадениями, чем VLOOKUP.

Существует возможность найти ближайшее соответствие, меньшее (-1) или ближайшее большее, чем (1) искомое значение. Существует также возможность использовать подстановочные знаки (2), такие как ? или *. Этот параметр не включен по умолчанию, как это было с функцией ВПР.

Формула в этом примере возвращает ближайшее меньшее значение, чем искомое, если точное совпадение не найдено: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

Однако в ячейке C7 есть ошибка, в которой возвращается ошибка #Н/Д (аргумент «если не найдено» не использовался). Это должно было вернуть скидку 0%, потому что расход 64 не соответствует критериям для любой скидки.

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

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

Формула сразу исправляет ошибку. Это не проблема с наличием «0» в нижней части диапазона.

Лично я бы по-прежнему сортировал таблицу по столбцу поиска. Наличие «0» внизу свело бы меня с ума. Но тот факт, что формула не нарушилась, гениален.

XLOOKUP также заменяет функцию HLOOKUP

Как уже упоминалось, функция XLOOKUP также здесь, чтобы заменить HLOOKUP. Одна функция вместо двух. Отлично!

Функция ГПР — это горизонтальный поиск, используемый для поиска по строкам.

Не так хорошо известен, как родственный ВПР, но полезен для примеров, подобных приведенному ниже, где заголовки находятся в столбце А, а данные — в строках 4 и 5.

XLOOKUP может смотреть в обоих направлениях — вниз по столбцам, а также по строкам. Нам больше не нужны две разные функции.

В этом примере формула используется для возврата значения продаж, относящегося к имени в ячейке A2. Он просматривает строку 4, чтобы найти имя, и возвращает значение из строки 5: =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP может смотреть снизу вверх

Как правило, вам нужно просмотреть список, чтобы найти первое (часто единственное) вхождение значения. XLOOKUP имеет шестой аргумент, называемый режимом поиска. Это позволяет нам переключить поиск, чтобы начать поиск снизу, и вместо этого искать список, чтобы найти последнее вхождение значения.

В приведенном ниже примере мы хотели бы найти уровень запасов для каждого продукта в столбце A.

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

Шестой аргумент функции XLOOKUP предоставляет четыре параметра. Нас интересует использование опции «Поиск в порядке очередности».

Завершенная формула показана здесь: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

В этой формуле четвертый и пятый аргумент игнорировались. Это необязательно, и мы хотели, чтобы по умолчанию было точное совпадение.

Округлять

Функция XLOOKUP является долгожданным преемником функций VLOOKUP и HLOOKUP.

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

Из-за того, что в Excel скоро появятся динамические массивы, он также может возвращать диапазон значений. Это определенно то, что стоит изучить дальше.

Дни ВПР сочтены. XLOOKUP уже здесь и скоро станет формулой поиска де-факто.