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

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

Что такое XLOOKUP?

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

На данный момент XLOOKUP доступен только пользователям программы предварительной оценки. Любой желающий может присоединиться к программе предварительной оценки, чтобы получить доступ к новейшим функциям 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)

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

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

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

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, где возвращается ошибка # N / A (аргумент «если не найден» не использовался). Это должно было дать скидку 0%, потому что расходы 64 не соответствуют критериям для какой-либо скидки.

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

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

Формула сразу исправляет ошибку. Если «0» находится внизу диапазона, это не проблема.

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

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

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

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

Не так хорошо известен, как его аналог VLOOKUP, но полезен для примеров, подобных ниже, где заголовки находятся в столбце A, а данные - в строках 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 появятся динамические массивы, он также может возвращать диапазон значений. Это определенно стоит изучить дальше.

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