Синтаксис XLOOKUP

XLOOKUP — современная замена VLOOKUP и HLOOKUP. Она находит значение в одном столбце (или строке) и возвращает соответствующее значение из другого.

Главное преимущество: Вы указываете где искать и что возвращать как два отдельных диапазона. Не нужно считать столбцы или беспокоиться о том, с какой стороны находится нужный результат.

XLOOKUP доступна в Excel 365, Excel 2021 и Google Таблицах.

Для простейшего XLOOKUP нужны всего три элемента:

• значение_для_поиска — то, что Вы ищете
• массив_для_поиска — диапазон, в котором выполняется поиск
• массив_результатов — диапазон, из которого нужно получить ответ

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

Представьте список товаров. В столбце A указаны названия товаров, а в столбце B — цены. Вам нужна цена «Клавиатуры».

Вы ищете название в диапазоне A2:A20, а цену возвращаете из диапазона B2:B20. XLOOKUP просматривает массив для поиска, находит «Клавиатуру», а затем возвращает значение из той же строки массива результатов.

Жёстко задавать «Клавиатура» внутри формулы ненадёжно. Гораздо лучше указывать в качестве искомого значения ссылку на ячейку.

Если пользователь вводит название товара в D2, формула автоматически реагирует на его изменение. Именно так работают интерактивные инструменты поиска и информационные панели.

Массив для поиска и массив результатов должны иметь одинаковую высоту (одинаковое количество строк). XLOOKUP сопоставляет их построчно.

Если в A2:A20 19 строк, массив результатов также должен содержать 19 строк. Несовпадение вызывает ошибку #VALUE!, поскольку XLOOKUP не может правильно сопоставить строки.

Массив результатов не обязан находиться рядом с массивом для поиска. Допустим, имена находятся в столбце A, а Вам нужно количество товара на складе из столбца E.

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

Одно из самых удобных свойств XLOOKUP — по умолчанию функция ищет точное совпадение.

В VLOOKUP по умолчанию использовалось приблизительное совпадение, которое незаметно возвращало неправильные результаты, если пользователь забывал последнее FALSE. XLOOKUP работает наоборот: Вы получаете точное совпадение, если специально не запросите другой вариант.

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

Используется та же структура формулы: искомое значение, строка с месяцами и строка с продажами. Одна функция заменяет и VLOOKUP, и HLOOKUP.

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

Например, умножьте найденную цену единицы товара на количество в E2. Сначала выполняется поиск и возвращается цена, затем умножение использует этот результат. Благодаря этому лист остаётся компактным и понятным.

Можно использовать ссылки на целые столбцы, например A:A и B:B, чтобы формула продолжала работать при добавлении новых строк.

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

По сравнению с VLOOKUP функция XLOOKUP:

• Не зависит от направления — возвращаемые столбцы могут находиться слева или справа от области поиска
• Надёжнее — точное совпадение используется по умолчанию
• Стабильнее — вставка столбцов не нарушает работу функции
• Проще — не нужно считать индексы столбцов

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

Проверьте, насколько хорошо Вы понимаете базовый синтаксис XLOOKUP.

Вы изучили три основных аргумента XLOOKUP:

• значение_для_поиска — что найти
• массив_для_поиска — где искать
• массив_результатов — что вернуть

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