XLOOKUP в Excel: синтаксис, примеры и чем она лучше VLOOKUP
By Mirko Peters · · 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.
- Щёлкните ячейку, в которой должен появиться результат, например E2.
- Введите
=XLOOKUP(D2,A2:A100,B2:B100). - Нажмите 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.