M365Con 2027 – бесплатная онлайн-конференция · 11–22 Jan 2027 · 120 sessions→ m365con.net

Блог · Mirko Peters

XLOOKUP в Excel: синтаксис, примеры и чем она лучше VLOOKUP

By · · Updated

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

Automatically translated with AI from the original post. Read the English original

XLOOKUP в Excel находит значение в одном диапазоне и возвращает соответствующий элемент из другого диапазона: =XLOOKUP(lookup_value, lookup_array, return_array). Введите табельный номер, укажите столбец с номерами, затем столбец с именами, и вы получите имя. Эта статья для тех, кто по привычке ещё пишет VLOOKUP или только что увидел XLOOKUP в файле коллеги. Здесь разобраны синтаксис, шесть аргументов, рабочие примеры для типичных задач на работе, сравнение с VLOOKUP и список ошибок, с которыми вы, скорее всего, столкнётесь.

Ключевые выводы

  • XLOOKUP принимает искомое значение, диапазон поиска и диапазон возврата: =XLOOKUP(lookup_value, lookup_array, return_array). Функция находит первое совпадение и возвращает элемент из диапазона возврата.
  • Она может искать слева от столбца поиска и по умолчанию ищет точное совпадение: VLOOKUP этого не умеет или по умолчанию делает иначе.
  • Четвёртый аргумент, if_not_found, заменяет ошибку #N/A текстом, который вы выбираете. Без него при отсутствии совпадения возвращается #N/A.
  • match_mode 2 включает подстановочные знаки, значения -1 и 1 находят ближайший меньший или больший элемент, а search_mode -1 ищет от последнего элемента к первому.
  • На странице Microsoft XLOOKUP указана для Microsoft 365, 2021 и 2024, а её примечания о Excel 2016 и 2019 противоречат друг другу, поэтому перед тем как полагаться на функцию, проверьте общие файлы.

Синтаксис и параметры ниже соответствуют странице Microsoft о функции XLOOKUP. Там, где шаблона нет на этой странице, в тексте об этом сказано. Это функция для Excel, и именно её Microsoft предлагает как более удачный вариант, когда вы открываете справку по VLOOKUP.

Синтаксис и шесть аргументов

Полная формула выглядит так:

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

Первые три аргумента обязательны. Последние три необязательны.

  • lookup_value — это то, что вы ищете, например табельный номер из ячейки D2. Если его не указать, XLOOKUP вернёт пустые ячейки, которые найдёт в массиве поиска.
  • lookup_array — это диапазон или массив для поиска, например A2:A100.
  • return_array — это диапазон или массив, из которого возвращается результат, например B2:B100.
  • if_not_found — это текст, который показывается, если подходящего совпадения нет. Если аргумент опущен, вы получите #N/A.
  • match_mode задаёт способ сопоставления. 0 — точное совпадение и значение по умолчанию. -1 означает точное совпадение или, если его нет, ближайший меньший элемент. 1 означает точное совпадение или ближайший больший элемент. 2 — сопоставление по шаблону, где *, ? и ~ имеют особое значение.
  • search_mode задаёт направление поиска. 1 ищет от первого элемента к последнему и используется по умолчанию. -1 ищет от последнего к первому. 2 и -2 выполняют двоичный поиск по данным, отсортированным по возрастанию или по убыванию. Microsoft предупреждает, что для двоичных режимов неотсортированные данные дают некорректные результаты.

Держите массив поиска и массив возвращаемых значений одинакового размера. Если массив поиска охватывает строки со 2 по 100, массив возврата тоже должен охватывать строки со 2 по 100. На странице Microsoft это прямо не сформулировано как правило, но несовпадающие диапазоны — обычная причина того, что поиск возвращает не ту строку или ошибку.

Простой поиск и аргумент «если не найдено»

Допустим, идентификаторы сотрудников находятся в диапазоне A2:A100, а имена — в диапазоне B2:B100. Вы вводите нужный идентификатор в ячейку D2.

  1. Щёлкните ячейку, в которой должен появиться результат, например E2.
  2. Введите =XLOOKUP(D2,A2:A100,B2:B100).
  3. Нажмите Enter.

Вы получите имя, которое соответствует идентификатору. XLOOKUP возвращает элемент для первого найденного совпадения. Если идентификатора нет, вы увидите #Н/Д.

Чтобы показать более понятное сообщение, добавьте четвёртый аргумент:

=XLOOKUP(D2,A2:A100,B2:B100,"No record found")

Теперь при отсутствии идентификатора в списке будет показано «No record found». Используйте это в таблицах, которые читают другие люди. Но учитывайте: такое сообщение может скрывать реальную проблему. Если в каждой строке написано «No record found», дело в диапазонах, а не в данных, поэтому сначала проверьте известный идентификатор.

Поиск влево

VLOOKUP не может вернуть значение из столбца, расположенного слева от столбца поиска. XLOOKUP может, потому что диапазоны поиска и возврата задаются отдельно. Если имя находится в столбце C, а нужный идентификатор — в столбце A:

=XLOOKUP(F2,C2:C100,A2:A100)

На странице Microsoft это сказано прямо: столбец поиска может находиться с любой стороны от столбца возврата.

Режимы сопоставления: подстановочные знаки и ближайшие значения

Совпадение по подстановочным знакам

Задайте для match_mode значение 2, чтобы использовать подстановочные знаки. Звёздочка обозначает любое количество символов, а вопросительный знак — ровно один символ. Тильда позволяет искать буквальные звёздочку или вопросительный знак.

=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)

Эта формула возвращает первую запись, которая начинается с «Jan». Если опустить 2, Excel будет искать буквальный текст «Jan*» и вернёт сообщение о том, что ничего не найдено. Это самая распространённая ошибка при работе с подстановочными знаками. Делайте шаблоны как можно более конкретными: XLOOKUP возвращает только первое совпадение, и слишком общий шаблон может найти не то, что вы имели в виду.

Следующее меньшее или большее значение

Режимы -1 и 1 предназначены для шкал: налоговых ставок, уровней комиссионных, ценовых диапазонов. Поместите пороги в A2:A6, ставки — в B2:B6 и ищите доход в E2:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)

При -1 Excel возвращает точное совпадение или, если его нет, ближайший меньший порог. Пустой аргумент перед -1 пропускает аргумент «если не найдено». В примере Microsoft для приблизительного сопоставления используется 1, чтобы вернуть налоговую ставку для ближайшего большего значения дохода, поэтому выбирайте направление, подходящее для вашей таблицы. В отличие от старого приблизительного VLOOKUP, пороги для этих режимов сортировать не нужно. Сортировка требуется только для режимов двоичного поиска.

Поиск с конца: последнее совпадение

Когда значение встречается несколько раз, XLOOKUP возвращает первое найденное. Чтобы получить последнее, выполните поиск с конца, задав для search_mode значение -1:

=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)

Если заказы в таблице расположены по дате, эта формула вернёт последнюю запись для клиента. 0 делает совпадение точным. Так решаются задачи вроде «последняя цена», «последний контакт» и подобные, которые с VLOOKUP были неудобны.

Возврат нескольких столбцов сразу

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

=XLOOKUP(D2,A2:A100,B2:D100)

Формула помещает совпадение из столбца B в ячейку, а значения из столбцов C и D выводит в ячейки справа. Оставьте для них место. Если справа уже есть данные, Excel покажет ошибку #ПЕРЕНОС! вместо того, чтобы их перезаписать. В примере Microsoft одна формула возвращает имя сотрудника и название отдела.

Двусторонний поиск

Microsoft показывает, как вложить две функции XLOOKUP, чтобы найти значение на пересечении строки и столбца. Шаблон со страницы:

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

Внутренняя функция выбирает столбец, который совпадает с заголовком в C3. Внешняя функция затем ищет D2 среди подписей строк и возвращает значение из этого столбца. Microsoft сравнивает это с комбинацией INDEX и MATCH. Если у вас отчёт, где товары перечислены слева, а месяцы идут сверху, эта формула заменяет более сложную конструкцию.

Несколько критериев

XLOOKUP принимает одно искомое значение, а на странице Microsoft нет примера с несколькими критериями. Распространённый приём — заставить Excel проверить два условия одновременно. Допустим, категория находится в A, статус — в B, а цена — в C. Чтобы найти цену для категории из F2 и статуса из G2:

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Not found")

Каждое сравнение возвращает TRUE или FALSE. Их произведение равно 1 только в тех строках, где оба условия выполнены, а XLOOKUP ищет первую единицу. Этот приём работает в актуальных версиях Microsoft 365, но его нет на странице функции Microsoft, поэтому проверьте его на небольшом образце. Альтернатива для старых версий — вспомогательный столбец, который объединяет два поля. Поставьте между значениями разделитель, например =A2&"|"&B2, и ищите объединённый текст таким же образом.

XLOOKUP и VLOOKUP рядом

На странице VLOOKUP Microsoft даёт синтаксис VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) и указывает, что искомое значение должно находиться в первом столбце диапазона. Если опустить range_lookup, VLOOKUP выполняет приблизительное сопоставление, которое предполагает сортировку первого столбца. Установите FALSE для точного совпадения. На той же странице рекомендуется попробовать XLOOKUP: он работает в любом направлении и по умолчанию возвращает точные совпадения.

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

  • =VLOOKUP(D2,A2:B100,2,FALSE)
  • =XLOOKUP(D2,A2:A100,B2:B100)

Различия, которые важны в повседневной работе:

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

Переписывать старые книги необязательно. Если VLOOKUP работает, оставьте его как есть. Используйте XLOOKUP в новых формулах и там, где нужен поиск влево, последнее совпадение или несколько столбцов результата.

Какие версии Excel поддерживают XLOOKUP

Здесь нужно быть внимательным. На странице Microsoft XLOOKUP указан для Excel для Microsoft 365 в Windows и Mac, Excel 2024, Excel 2021, а также для приложений Excel на iPad, iPhone и Android. Примечание на той же странице говорит, что функции нет в Excel 2016 и 2019, но она может появиться в книгах, созданных в более новых версиях. При этом в строке «применимо к» на той же странице указаны 2016 и 2019. Информация противоречива, поэтому не считайте Excel 2016 и 2019 безопасными. Excel для веба на этой странице тоже не упомянут.

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

Частые ошибки и сбои

  • Забытый аргумент match_mode, равный 2, при использовании подстановочных знаков. Тогда поиск ищет буквальные символы.
  • Двоичный search_mode (2 или -2) на несортированных данных. Microsoft указывает, что результаты в этом случае недействительны.
  • Диапазоны поиска и возврата разной длины. Сделайте их одинаковыми.
  • Скрытые пробелы и числа, сохранённые как текст. «100» и 100 — разные значения, поэтому поиск не срабатывает и вы видите #N/A. Очистите данные с помощью TRIM или преобразуйте числа.
  • Скрытие проблем за понятным сообщением об отсутствии результата. Перед тем как полагаться на формулу, проверьте её на одном известном значении.
  • Диапазон возврата, который указывает на ячейки, куда должен выводиться результат с переносом, и которые при этом заняты.
  • Ссылки на весь столбец на очень больших листах. Используйте ограниченный диапазон или таблицу Excel, чтобы Excel обрабатывал меньше ячеек.

Связанные темы для изучения

  • XLOOKUP и INDEX с MATCH. Обе связки выполняют поиск влево, а пара INDEX и MATCH работает и в старых версиях Excel. Почитайте об этом, если вы передаёте файлы людям с Excel 2016 или 2019.
  • Несколько результатов. XLOOKUP возвращает только первое совпадение. Чтобы получить все совпадения, посмотрите на функцию FILTER.
  • XLOOKUP с таблицами Excel. Структурированные ссылки позволяют диапазонам расти вместе с данными, и такие формулы легко читать.
  • Данные из Power Query. После загрузки данных в таблицу XLOOKUP может искать в ней так же, как в любой другой таблице. Сначала обновите запрос, а затем проверьте результаты.
  • Excel для Интернета. На странице Microsoft о XLOOKUP этот вариант не упоминается, поэтому перед тем как делиться файлом, проверьте в своей среде, работает ли функция у ваших коллег.

Следующий шаг

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

Источники

Entities in this article

Microsoft Excel Excel for the web

Related articles

RSS