В зависимости от ваших аргументов XLookup покажет вам первое или последнее значение в случаях с более чем одним совпадающим результатом.

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

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

Преимущества XLookup в Excel

Самым большим преимуществом XLookup в Excel является экономия времени. Например, вы можете получить результат поиска, не изменяя позиционирование данных.

Как и другие формулы поиска Excel, он экономит огромное количество времени, которое в противном случае было бы потрачено на ручную прокрутку строк и строк данных.

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

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

Аргументы в пользу XLookup также проще, чем VLookup, что делает его быстрее и проще в использовании. Например, XLookup по умолчанию использует точное совпадение, тогда как вы должны указать это в аргументе VLookup.

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

XLookup Обязательные аргументы

Ваша функция XLookup требует трех аргументов:

• Lookup_value: это значение, которое вы ищете в массиве.
• Lookup_array: это диапазон ячеек, в которых вы хотите, чтобы возвращаемое значение отображалось.
• Return_array: это диапазон ячеек, в которых вы хотите, чтобы функция искала значение.

Таким образом, простая версия XLookup будет выглядеть так:

=XLOOKUP(искомое значение, искомый массив, возвращаемый массив)

XLookup Необязательные аргументы

Функция XLookup также имеет несколько необязательных аргументов, которые можно использовать для более сложных сценариев или для сужения поиска:

• Match_mode: определяет тип используемого соответствия, например точное совпадение или подстановочный знак для получения частичных совпадений.
• Search_mode: определяет, должна ли ваша функция выполнять поиск слева направо или наоборот.
• If_not_found: этот аргумент сообщает функции, какое значение следует вернуть, если совпадений вообще нет.

Как использовать XLookup в Excel

1. Откройте Excel и электронную таблицу, для которой вы хотите использовать функцию XLookup.

2. Выберите ячейку, в которую вы хотите поместить формулу XLookup Excel.

3. Введите «=» в этой ячейке, а затем введите «XLookup». Нажмите на опцию «XLookup», которая появляется в раскрывающемся меню.

4. Открывающая скобка создается автоматически. После открывающей скобки введите необходимые аргументы в правильном порядке: искомое_значение, искомое_массив и возвращаемый_массив.

Это означает выбор диапазона ячеек для каждого аргумента и постановку запятой перед переходом к следующему аргументу.

5. Если вы используете необязательные аргументы, введите их после обязательных аргументов.

6. Когда ваши аргументы XLookup будут завершены, добавьте закрывающую скобку перед нажатием клавиши ввода.

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

В этом примере Excel XLookup мы использовали XLookup, чтобы узнать оценку конкретного учащегося. Таким образом, искомое_значение было именем студента («Рубен Пью»).

Lookup_array представлял собой список имен учащихся в столбце A. Наконец, lookup_return представлял собой список оценок учащихся в столбце C. С помощью этой формулы функция возвращала значение оценки ученика: C-.

Но как использовать XLookup с необязательными аргументами?

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

Здесь мы просим Excel вернуть сообщение «Не найдено», если он не может получить значение, указывающее, что студент «Смит» отсутствует в этом списке:

Теперь предположим, что мы хотим узнать, была ли у кого-то из студентов посещаемость 70%. Но мы также хотим знать следующий ближайший процент посещаемости, если 70% не найдено в наборе данных.

Рекомендации по использованию XLookup в Excel

Используйте описательные ссылки

Предпочтительно использовать описательные ссылки для используемых ячеек, а не общие диапазоны, такие как A1:A12.

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

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

Используйте точные совпадения, где это возможно

Использование значения по умолчанию с точным соответствием в формуле (в отличие от подстановочных знаков) помогает гарантировать, что вы не получите непредусмотренные значения в возвращаемых значениях.

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

Сохраняйте диапазоны аргументов одного размера

При составлении аргументов убедитесь, что вы используете одинаковое количество ячеек для возвращаемого массива и массива поиска. Если нет, вы получите сообщение об ошибке, и Excel просто вернет #ЗНАЧ в ячейке.

Проверьте свою формулу

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

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

Начиная

Являясь новым и улучшенным способом использования функции поиска в Excel, XLookup превосходит классический VLookup по нескольким параметрам.

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