Раньше я регулярно сомневался в надёжности формул Excel: ошибись с номером столбца или диапазоном — и отчёт ломается. Но стоило мне начать пользоваться XLOOKUP вместо VLOOKUP — работа наконец стала предсказуемой, а формулы больше не пугают неожиданными ошибками.
- Простой синтаксис XLOOKUP: вот как я наконец понял, как работает Excel
- Как устроена современная формула поиска
- С VLOOKUP я считал столбцы как робот
- Как смена порядка столбцов рушила диссертации (и обычные формулы)
- Больше не надо изобретать IFERROR: XLOOKUP сам справится с ошибками!
- Как элегантно обработать отсутствие данных
- Точное совпадение по умолчанию — забудьте про подводные камни!
- Что значит больше не указывать FALSE в конце каждой формулы
- Нужно искать с конца? Теперь это быстрее простого!
- Как за секунду найти свежую запись в самом низу таблицы
- Теперь можно вытянуть сразу несколько столбцов одной формулой!
- Как одним махом получить все данные по сотруднику
- Пора переходить на умные формулы — это реально экономит нервы!
Простой синтаксис XLOOKUP: вот как я наконец понял, как работает Excel
Как устроена современная формула поиска
Прежде чем перейти к преимуществам XLOOKUP, напомню, почему многие так привыкли к VLOOKUP: данные обычно идут по столбцам.
VLOOKUP всегда требует четыре обязательных параметра: что искать, где искать, номер столбца и явно указанный FALSE для точного совпадения — иначе результат может быть случайным:
XLOOKUP сразу же избавляет от этих неудобств. Не нужно больше выделять весь диапазон или вручную высчитывать номера столбцов: просто указываешь, что искать, где искать и что вернуть — и всё работает!
Дальше покажу, насколько легко стало работать с точным совпадением и поиском с конца таблицы.
Особенно удобно использовать XLOOKUP, если ваши данные оформлены как «Таблица» (Ctrl+T или «Вставка» → «Таблица») — так вместо номеров используются имена столбцов, а формулы становятся нагляднее. Я буду показывать примеры на таблице сотрудников StaffDirectory, где есть пять столбцов: ID, ФИО, отдел, должность и Email.
С VLOOKUP я считал столбцы как робот
Как смена порядка столбцов рушила диссертации (и обычные формулы)
Меня ужасно раздражал этот ручной подсчёт столбцов в VLOOKUP. Например, если нужно найти email по имени из ячейки A2, нельзя напрямую обратиться к столбцу — VLOOKUP ищет только в первом столбце диапазона, а это часто ID. В итоге формула не срабатывает — ошибка:
Чтобы формула всё-таки сработала, диапазон приходится менять, чтобы нужный столбец оказался первым. И тут появляются ошибки: если обозначить не тот номер — вместо email появится, например, название отдела или должность.
Попробуйте только забыть, что после изменения диапазона индекс столбца тоже меняется — получите #Н/Д. А если кто-то добавит столбец внутри диапазона, все расчёты слетят.
С XLOOKUP эти головоломки ушли в прошлое. Формула читается буквально как предложение:
Теперь можно переставлять или добавлять столбцы, как угодно — формула работает всегда, ведь она опирается на имена столбцов, а не их положение.
А как насчёт HLOOKUP? Если данные разложены не по столбцам, а по строкам, раньше приходилось выкручиваться через HLOOKUP. Сейчас разницы больше нет: XLOOKUP одинаково хорошо ищет и по строкам, и по столбцам — синтаксис всегда один.
Microsoft 365 Personal
Microsoft 365 — это Word, Excel и PowerPoint сразу на пяти устройствах, плюс 1 ТБ в облаке OneDrive и много других полезных вещей.
Больше не надо изобретать IFERROR: XLOOKUP сам справится с ошибками!
Как элегантно обработать отсутствие данных
VLOOKUP при отсутствии значения сразу выдаёт ошибку. В XLOOKUP прямо внутри формулы можно прописать, какое сообщение показывать, если данных нет.
Чтобы скрыть страшный #Н/Д в VLOOKUP, приходилось оборачивать формулу в IFERROR — только больше путаницы и длинных формул:
С XLOOKUP всё проще: указываешь понятное сообщение прямо в четвёртом параметре — и если сотрудник не найден, формула напишет ровно то, что вы указали:
Формула не становится длиннее, а понять, что она делает, становится на порядок проще.
Точное совпадение по умолчанию — забудьте про подводные камни!
Что значит больше не указывать FALSE в конце каждой формулы
Самая коварная ловушка VLOOKUP: если забыть последний параметр, функция ищет похожее, а не точное совпадение. Итог — сюрпризы в данных.
Например: ищете «1065», а в списке есть только «1064» — VLOOKUP без явно указанного FALSE вернёт не ошибку, а сотрудника с ID 1064. И вы это даже не сразу заметите!
Вторая проблема — если таблица не отсортирована по возрастанию, VLOOKUP может возвращать вообще что угодно или выдавать #Н/Д. Разобраться в причинах почти невозможно.
В XLOOKUP совпадение всегда точное по умолчанию. Даже если ничего не указать, формула найдет только то значение, которое нужно, а сортировка столбца не важна:
Если искать «1065», а такого ID нет, XLOOKUP честно напишет «ID не найден» — и никаких ложных совпадений и сюрпризов!
Нужно искать с конца? Теперь это быстрее простого!
Как за секунду найти свежую запись в самом низу таблицы
Когда таблица растёт, часто нужно узнать не первую, а последнюю запись (например, самый актуальный отдел для сотрудника). Это сильная сторона XLOOKUP.
Вот пример: в нашей таблице сотрудник «Marcus Vance» встречается дважды — сначала работал в «Маркетинге» (первая строка), потом перешёл в «Продажи» (пятая строка). Самое новое — последняя строка.
Но обычный VLOOKUP всегда покажет только самое первое совпадение (то есть устаревший отдел):
В XLOOKUP для поиска снизу достаточно указать -1 в шестом аргументе, и формула сразу выдаёт самую свежую информацию:
Теперь всегда получаете актуальные данные, даже если их несколько!
Теперь можно вытянуть сразу несколько столбцов одной формулой!
Как одним махом получить все данные по сотруднику
Раньше для этого приходилось писать отдельный VLOOKUP на каждую ячейку — отдельно для отдела, отдельно для должности, отдельно для email:
и
и
С динамическими диапазонами (dynamic arrays) достаточно одной формулы XLOOKUP — выделяешь все нужные столбцы, и Excel сам заполнит их в соседних ячейках:
Чем меньше формул в файле, тем проще его поддерживать и тем быстрее работает сам Excel — особенно на больших таблицах.
А для сложных случаев можно воспользоваться CHOOSECOLS, если нужны не соседние столбцы — но чаще всего XLOOKUP хватает за глаза.
Пора переходить на умные формулы — это реально экономит нервы!
После того как я полностью перешёл на XLOOKUP, мои таблицы перестали ломаться на пустом месте. Я больше не переживаю за сбитые индексы или странные совпадения. Теперь работаю с данными, а не бесконечно чиню формулы. Кстати, если привыкли к связке INDEX+MATCH, новая функция XMATCH тоже поддерживает точное совпадение по умолчанию — попробуйте, это удобно!
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка — это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!
Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru
