Все формулы РФ

Excel и Google Workspace / Поиск и подстановка

XLOOKUP для точного поиска в Excel

XLOOKUP ищет значение из E2 в диапазоне A2:A20 и возвращает соответствующее значение из C2:C20. В отличие от VLOOKUP, диапазон поиска и диапазон результата задаются отдельно, поэтому функция спокойно ищет как вправо, так и влево.

Опубликовано: Обновлено:

Поиск и подстановкаОфисные расчетыExcelЕсть историческая справкаСтраницы с задачами и решениями

Формула

$$=XLOOKUP(E2,A2:A20,C2:C20,"Не найдено",0)$$

Обозначения

$E2$
значение, которое нужно найти
$A2:A20$
одномерный диапазон поиска с ключами
$C2:C20$
диапазон результата той же высоты, из которого возвращается значение
"Не найдено"
текст, который выводится, если ключ отсутствует
$0$
режим точного совпадения

Условия применения

  • Диапазон поиска и диапазон результата должны иметь одинаковое количество строк или столбцов.
  • Для текстовых ключей, артикулов и кодов используйте match_mode 0, чтобы исключить приблизительный подбор.
  • Функция доступна в современных версиях Excel и Google Sheets, но старые книги Excel могут не поддерживать ее.

Ограничения

  • В старых версиях Excel, например в Excel 2016 без Microsoft 365, XLOOKUP может быть недоступна.
  • При повторяющихся ключах возвращается первое найденное совпадение, если не задан другой режим поиска.
  • Функция не исправляет различия в типах данных: текстовый код "1001" и числовое значение 1001 могут не совпасть.

Подробное объяснение

XLOOKUP разделяет поиск и возврат результата. Сначала она определяет позицию значения E2 внутри A2:A20, затем берет элемент с той же позицией из C2:C20.

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

Четвертый аргумент отвечает за текст при отсутствии совпадения. Вместо #N/A можно сразу показать "Не найдено", что делает отчет понятнее для коллег, которые не работают с формулами каждый день.

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

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

Как пользоваться формулой

  1. Поместите искомый ключ в отдельную ячейку, например E2.
  2. Выберите диапазон, где этот ключ должен быть найден.
  3. Выберите диапазон результата такой же длины.
  4. Добавьте текст для отсутствующего значения, чтобы отчет не показывал сырую ошибку.
  5. Укажите 0 последним аргументом для точного поиска.

Историческая справка

XLOOKUP появилась как современная замена многим сценариям VLOOKUP, HLOOKUP и INDEX с MATCH. Она устранила несколько типичных неудобств старых функций поиска.

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

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

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

Распространение XLOOKUP совпало с переходом Excel к динамическим массивам и более гибким формулам, поэтому функция воспринимается как часть нового подхода к справочникам.

Историческая линия формулы

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

Пример

Дано: A2:A5 содержит коды A-10, B-20, C-30, D-40, а C2:C5 содержит цены 1250, 1840, 990, 2100. В E2 записано C-30. Формула =XLOOKUP(E2,A2:A5,C2:C5,"Не найдено",0) находит C-30 в A4 и возвращает C4. Результат: 990. Если заменить E2 на D-40, функция вернет 2100, потому что диапазон результата C2:C5 совпадает по позициям с диапазоном поиска. Если ввести Z-99, четвертый аргумент сработает как готовый ответ и на листе появится текст "Не найдено". Это удобнее, чем отдельно обрабатывать #N/A в соседней формуле.

Частая ошибка

Самая частая ошибка: задать диапазоны разной длины, например A2:A20 и C2:C18. Excel вернет ошибку, потому что не сможет сопоставить позиции. Вторая ошибка: забыть match_mode 0 и получить неподходящее поведение при числовых диапазонах. Третья ошибка: ожидать, что аргумент "Не найдено" сработает при любой ошибке. Он заменяет отсутствие совпадения, но не исправляет неверные диапазоны и поврежденные ссылки.

Практика

Задачи с решением

Вернуть менеджера по номеру заявки

Условие. В A2:A4 номера заявок: Z-101, Z-102, Z-103. В C2:C4 менеджеры: Анна, Борис, Мария. В E2 введено Z-103.

Решение. Формула =XLOOKUP(E2,A2:A4,C2:C4,"Не найдено",0) находит Z-103 на третьей позиции диапазона A2:A4 и возвращает третий элемент из C2:C4.

Ответ. Мария

Показать понятный текст при отсутствии

Условие. В E2 введено Z-999, такого номера нет в A2:A4. Нужно вывести не ошибку, а понятное сообщение.

Решение. Та же формула =XLOOKUP(E2,A2:A4,C2:C4,"Не найдено",0) использует четвертый аргумент, потому что точного совпадения нет.

Ответ. Не найдено

Дополнительные источники

  • Microsoft Support: XLOOKUP function - https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
  • Microsoft Support: Excel functions by category - https://support.microsoft.com/en-us/office/excel-functions-by-category-5f91f4e9-7b42-46d2-9bd1-63f26a86c0eb
  • Dan Bricklin: VisiCalc and the origins of spreadsheets - https://www.bricklin.com/visicalc.htm

Связанные формулы

Excel и Google Workspace

VLOOKUP для точного поиска в Excel

$=VLOOKUP(E2,A2:C20,3,FALSE)$

Функция VLOOKUP ищет значение из E2 в первом столбце диапазона A2:C20 и возвращает значение из третьего столбца найденной строки. Последний аргумент FALSE включает точное совпадение, поэтому формула подходит для артикулов, кодов клиентов и других ключей, где приблизительный поиск недопустим.

Excel и Google Workspace

INDEX и MATCH для поиска значения слева

$=INDEX(A2:A20,MATCH(E2,C2:C20,0))$

Связка INDEX и MATCH ищет значение E2 в C2:C20, определяет номер найденной позиции и возвращает значение с той же позиции из A2:A20. Такой прием особенно полезен, когда результат расположен левее столбца поиска.

Excel и Google Workspace

SUMIFS для суммы по нескольким условиям

$=SUMIFS(D2:D100,B2:B100,"Москва",C2:C100,">="&DATE(2026,1,1))$

SUMIFS суммирует значения из D2:D100 только по тем строкам, где город в B2:B100 равен Москве, а дата в C2:C100 не раньше 1 января 2026 года. Функция подходит для отчетов продаж, расходов и оплат с несколькими фильтрами.

Excel и Google Workspace

COUNTIFS для подсчета строк по нескольким условиям

$=COUNTIFS(B2:B100,"Москва",D2:D100,">5000")$

COUNTIFS считает строки, которые одновременно удовлетворяют нескольким условиям. В этой формуле учитываются только строки, где в столбце B указана Москва, а значение в столбце D больше 5000.