Вступление
Многие ожидают, что функция XLOOKUP() в Excel должна работать быстрее, чем VLOOKUP(), если обе используются для поиска и возврата одинакового результата. На практике дело сложнее: в одном большом файле (6 МБ) с множеством заполненных формул замена VLOOKUP() на XLOOKUP() замедлила время пересчёта с ~2 секунд до ~4,5 секунд.
Чтобы проверить поведение, был создан небольшой тест: в столбце B записаны числа 1–1 048 576, в столбце C — их квадратные корни (от 1 до ~1000). Поиск значения 1 000 000 в B и возврат соответствующего значения в C (1000) показал: XLOOKUP() — 0,04–0,05 с, VLOOKUP() — 0,00 с. В сети есть противоречивые данные: одни источники утверждают, что XLOOKUP быстрее, другие — что VLOOKUP быстрее. В этой статье разберём, в каких ситуациях XLOOKUP действительно может быть быстрее.
Коротко о ключевых различиях
XLOOKUP в целом более гибкий и функциональный, имеет больше опций и по умолчанию выполняет точное совпадение (exact match). VLOOKUP обладает меньшим набором опций и по умолчанию использует приблизительное совпадение (approximate match), что иногда даёт преимущество по скорости, но снижает точность.
Важно понимать, что скорость — не единственный критерий выбора. XLOOKUP предлагает удобство (например, безопаснее при удалении столбцов) и дополнительные режимы поиска, которые позволяют оптимизировать производительность в конкретных сценариях.
Тесты и наблюдения
Реальный большой файл
В большом рабочем файле (примерно 6 МБ) с множеством формул замена всех VLOOKUP на XLOOKUP привела к увеличению времени пересчёта с ~2 секунд до ~4,5 секунд. Это демонстрирует, что на реальных данных поведение функций может зависеть от многих факторов: структуры диапазонов, количества столбцов и организации формул.
Небольшой тест на 1 048 576 строк
В эксперименте со столбцами B (1–1 048 576) и C (sqrt(B)) при поиске 1 000 000 результат 1000 возвращался: XLOOKUP() — 0,04–0,05 секунды, VLOOKUP() — 0,00 секунды. Несмотря на этот пример, в других условиях XLOOKUP может показывать преимущество; источники по разному оценивают производительность в зависимости от сценария.
Когда XLOOKUP быстрее VLOOKUP
1. Обратные (right-to-left) поиски и замена INDEX+MATCH
VLOOKUP не выполняет нативно поиск справа-налево, поэтому для обратных поисков часто используют комбинацию INDEX+MATCH. XLOOKUP поддерживает обратный поиск напрямую, что упрощает формулы и повышает эффективность при больших наборах данных.
2. Поиск по одному столбцу (single-column matching)
XLOOKUP загружает в оперативную память только столбец поиска и столбец возврата, тогда как VLOOKUP обрабатывает весь указанный диапазон. На больших таблицах это может существенно снизить объем обрабатываемых данных и ускорить выполнение. Пример:
=XLOOKUP(«Order123», Orders[ID], Orders[Amount]) — обрабатывает только два столбца.
=VLOOKUP(«Order123», Orders, 5, FALSE) — обрабатывает весь диапазон Orders.
3. Бинарный поиск для отсортированных данных (search_mode=2)
Если данные заранее отсортированы, XLOOKUP поддерживает бинарный поиск через параметр search_mode=2. Бинарный поиск делит диапазон пополам и выполняет логарифмическое число шагов, что гораздо быстрее линейного перебора. XLOOKUP в режиме бинарного поиска может быть более чем в 2 раза быстрее, чем XLOOKUP в «обычном» режиме; по сложности это логарифмический vs линейный поиск.
4. Динамические массивы и уменьшение повторных вычислений
XLOOKUP нативно поддерживает динамические массивы, что позволяет возвращать массивы значений без обёрток. VLOOKUP для похожих задач требует дополнительных функций (например, FILTER), что может увеличить вычислительную нагрузку и замедлить расчёт при большом объёме данных.
Краткое резюме
XLOOKUP обычно более мощный и гибкий, и в ряде конкретных случаев может работать быстрее, особенно при обратных поисках, при поиске по одной колонке, при использовании бинарного поиска (search_mode=2) и при работе с динамическими массивами. В то же время VLOOKUP может быть быстрее в простых сценариях из‑за меньшего набора опций и поведения по умолчанию.
Выбор между XLOOKUP и VLOOKUP — это компромисс между скоростью в конкретном сценарии и общей гибкостью/точностью формул.
Источники
Performance of XLOOKUP: How fast is the new XLOOKUP vs. VLOOKUP?
