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

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

Как работает XLOOKUP

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

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

Параметры функции XLOOKUP следующие:

= XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])

• искомое_значение: Значение, которое вы хотите найти
• поисковый_массив: Массив (столбец), который вы хотите найти
• return_array: Результат (столбец), из которого вы хотите получить значение
• match_mode (необязательно): Выберите точное совпадение (0), точное совпадение или следующее наименьшее значение (-1) или совпадение с подстановочным знаком (2).
• search_mode (необязательно): Выберите, выполнять ли поиск, начиная с первого элемента в столбце (1), последнего элемента в столбце (-1), двоичного поиска по возрастанию (2) или двоичного поиска по убыванию (-2).

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

Как искать один результат с помощью XLOOKUP

Самый простой способ использования XLOOKUP — это поиск одного результата с использованием точки данных из одного столбца.

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

   
   




2. Если вы хотите найти первую продажу в списке, представленном конкретным торговым представителем, вы можете создать функцию XLOOKUP, которая ищет имя в столбце Rep. Функция вернет результат из столбца Всего. Функция XLOOKUP для этого:

   = XLOOKUP (I2, С2: С44, G 2: G44,0,1)
   

   • I2: Указывает на Имя Реп поисковая ячейка
   • С2: С44: Это столбец Rep, который является поисковым массивом
   • G2: G33: Это столбец Total, который является возвращаемым массивом
   • : Выбирает точное совпадение
   • 1: Выбирает первое совпадение в результатах




3. Когда вы нажимаете Войти и введите имя торгового представителя, ячейка Итоговый результат покажет вам первый результат в таблице для этого торгового представителя.

   
   




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

   
   




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

   = XLOOKUP (I2, D2: D44, С2: C44,0,1)
   

   • D2: Указывает на ячейку поиска элемента
   • D2: D44: Это столбец Item, который является массивом поиска
   • С2: С44: Это столбец Rep, который является возвращаемым массивом слева от поискового массива
   • : Выбирает точное совпадение
   • 1: Выбирает первое совпадение в результатах




6. На этот раз результатом будет имя торгового представителя, который продал первый заказ на переплет за год.

   
   

Выполните вертикальное и горизонтальное соответствие с XLOOKUP

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

Эта функция двойного поиска является эффективной заменой другим функциям Excel, таким как INDEX, MATCH или HLOOKUP.

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

   
   




2. С функцией XLOOKUP этот вид поиска прост. Используя следующую вспомогательную функцию XLOOKUP, вы можете искать продажи в третьем квартале для конкретного торгового представителя:

   = XLOOKUP (J2, В2: В42, XLOOKUP (К2, С1: H1, С2: H42))
   

   • J2: Указывает на ячейку поиска Rep
   • B2: B42: Это столбец Item, который является массивом поиска столбцов
   • K2: Указывает на ячейку поиска Quarter
   • С1: Н1: Это массив поиска строк
   • С2: H42: Это массив поиска для суммы в долларах за каждый квартал

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




3. Результатом по этой формуле является доход за первый квартал для представителя с именем Томпсон.

   
   

Использование функции XLOOKUP

Функция XLOOKUP доступна только для подписчиков Office Insider, но вскоре будет распространена на всех подписчиков Office 365.

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

Как только вы присоединитесь к программе Office Insider, ваша установленная версия Excel получит все последние обновления, и вы сможете начать использовать функцию XLOOKUP.