XLOOKUP(): самая универсальная функция поиска в Excel

Если вы когда-либо использовали VLOOKUP() и застревали из‑за необходимости искать значение слева, XLOOKUP() решает эту проблему. Это новая функция поиска в Excel. Она может заменить VLOOKUP(), HLOOKUP() и даже большинство формул INDEX() и MATCH() одной простой формулой.

XLOOKUP() может искать в любом направлении, возвращать сразу несколько столбцов и отображать настраиваемое сообщение, если значение отсутствует. 

В этом руководстве мы сначала разберём синтаксис. Затем шаг за шагом построим реальные формулы, используя один простой набор данных на протяжении всего пути. Также рассмотрим точные и приблизительные совпадения, возврат нескольких столбцов, обработку ошибок и сравнение XLOOKUP() с VLOOKUP() и INDEX()/MATCH(). 

Как использовать XLOOKUP() в Excel

Давайте создадим рабочую формулу XLOOKUP() с нуля. 

В нашем наборе данных пять товаров, у каждого есть ID, название, категория и цена, в столбцах A–D, с заголовками в строке 1.

Набор данных для XLOOKUP(). Изображение автора.

Предположим, мы хотим найти цену для «P104». Пройдёмся по шагам.

Поиск одного значения

Щёлкните пустую ячейку, где должен появиться результат. Я использую F2. И введите следующую формулу:

=XLOOKUP("P104", A2:A6, D2:D6)

В этой формуле: 

• «P104» — это lookup_value, то, что мы ищем

• A2:A6 — это lookup_array, диапазон, в котором Excel ищет

• D2:D6 — это return_array, диапазон, из которого Excel берёт ответ

Excel ищет «Product ID» в столбце A, находит «P104» в строке 5 и возвращает соответствующее значение из столбца D: $45.

Вместо прямого ввода «P104» можно сослаться на ячейку, чтобы менять искомое значение без редактирования самой формулы. В нашем случае ячейка A5 содержит «P104», поэтому формула будет такой:

=XLOOKUP(A5, A2:A6, D2:D6)

Использование XLOOKUP() в Excel. Изображение автора.

Теперь вы можете ввести любой ID продукта в A5, и цена обновится автоматически.

Возврат значения из другого столбца

На этом шаге видно ключевое отличие XLOOKUP() от VLOOKUP(). Вы не привязаны к возврату значения только из столбца справа от столбца поиска.

Скажем, вместо цены вам нужна категория для «P102». Настройка та же, просто укажите return_array на столбец C, «Category»:

=XLOOKUP(A3, A2:A6, C2:C6)

Здесь A3 возвращает Home. Если изменить на A5, обновится на Electronics. Аналогично, поменяйте return_array, чтобы получить название товара, цену или любой другой столбец — при тех же lookup_value и lookup_array.

XLOOKUP() возвращает значение из другого столбца. Изображение автора.

С VLOOKUP() вам пришлось бы отсчитывать столбцы от столбца поиска и каждый раз настраивать индекс столбца. В XLOOKUP() вы просто указываете нужный столбец напрямую. 

Копирование формулы на несколько строк

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

Вот как это настроить: столбец A, начиная с A2, уже содержит список Product ID. Мы хотим, чтобы соответствующие цены появились рядом с каждым, в столбце F.

Для этого щёлкните F2 и введите формулу, ссылаясь на A2 как на искомое значение:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Теперь снова нажмите на F2 и перетащите маркер заполнения (маленький квадрат в правом нижнем углу ячейки) вниз по остальным строкам.

Excel скопирует формулу вниз, и каждая строка вернёт правильную цену для своего Product ID. ID из A2 получит цену в F2, из A3 — в F3 и так далее.

Скопируйте формулу в другие ячейки. Изображение автора.

Обратите внимание на знаки доллара вокруг A2:A6 и D2:D6. Это называется абсолютной ссылкой. Она фиксирует диапазон, чтобы он не сдвигался при копировании формулы вниз. 

Здесь это важно, поскольку каждой строке нужно искать в одном и том же списке продуктов, меняется только искомое значение.

Как использовать точное и приблизительное совпадение в XLOOKUP()

По умолчанию XLOOKUP() ищет точное совпадение. Если его нет, возвращается ошибка, если вы не настроили аргумент if_not_found (мы рассматривали его ранее). 

Но XLOOKUP() также может находить ближайшее совпадение, используя аргумент match_mode. 

Точное совпадение (по умолчанию)

Дополнительно ничего вводить не нужно. Все формулы, которые мы строили до сих пор, уже используют точное совпадение, так как это поведение по умолчанию для XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel ищет точное значение из A2 и возвращает результат только если найдёт именно такое совпадение где‑то в диапазоне поиска.

Если хотите прописать это явно, добавьте ноль в качестве четвёртого аргумента:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() возвращает точное совпадение. Изображение автора.

Замечаете пробел перед 0? Это позволяет пропустить if_not_found (мы оставляем его пустым) и перейти сразу к match_mode. Чаще всего этот аргумент вовсе можно опустить и позволить Excel по умолчанию использовать точное совпадение.

Приблизительное совпадение

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

Здесь помогает приблизительное совпадение. 

Установите match_mode в -1, и XLOOKUP() найдёт наибольшее значение, которое меньше или равно искомому.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Предположим, ваш диапазон содержит пороги продаж $0, $5 000 и $10 000, каждый привязан к своей ставке комиссии. 

Представитель с продажами $7 500 не совпадает ни с одной строкой точно. С match_mode, равным -1, XLOOKUP() найдёт $5 000 — наибольший порог, не превышающий $7 500, — и вернёт связанную с ним ставку.

Если вместо этого установить match_mode в 1, XLOOKUP() перевернёт логику: он найдёт наименьшее значение, которое больше или равно искомому. Это полезно, например, для весовых категорий доставки, когда вы округляете вверх до следующего уровня.

XLOOKUP() возвращает приблизительное совпадение. Изображение автора.

Примечание: приблизительное совпадение предполагает, что диапазон поиска отсортирован: по возрастанию для -1 и по убыванию для 1. Если сортировка неверная, XLOOKUP() может вернуть неправильный результат без ошибки, что ещё хуже. Поэтому всегда проверяйте порядок сортировки, прежде чем полагаться на это.

Как искать значения слева с помощью XLOOKUP()

VLOOKUP() ищет только в левом столбце диапазона и возвращает значение из столбца справа; в другую сторону он не работает. Его третий аргумент — это не диапазон, а индекс столбца, считанный от левой границы. 

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

XLOOKUP() лишён этого ограничения, так как вы указываете отдельные диапазоны для поиска и возврата.

Возьмём наш список продуктов: Product ID в столбце A, Product Name — в столбце B. 

Предположим, у вас есть название товара, и вы хотите найти его ID — обратная задача по сравнению с тем, что умеет VLOOKUP().

В C9 введите Bluetooth Speaker (название, которое есть в списке). В E2 введите:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() ищет «Bluetooth Speaker» в столбце B, находит его в строке 5 и возвращает «P104» из столбца A, хотя столбец A находится слева от B. 

XLOOKUP() ищет значения слева, в отличие от VLOOKUP(). Изображение автора.

Как вернуть несколько столбцов с помощью XLOOKUP()

XLOOKUP() может возвращать сразу несколько столбцов одной формулой, используя динамические массивы. Для этого укажите в return_array диапазон, а не один столбец, и XLOOKUP() «разольёт» всю подходящую строку по необходимому числу ячеек. 

Попробуем на нашем списке продуктов. 

В C9 я ввожу Product ID, например «P104». В E2 ввожу:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Обратите внимание: return_array здесь — B2:D6, шириной в три столбца, охватывает Product Name, Category и Price. После нажатия Enter XLOOKUP() находит «P104» в столбце A, затем одновременно возвращает Bluetooth Speaker, Electronics и $45, «разливая» их в E9, F9 и G9.

XLOOKUP() возвращает несколько столбцов одной формулой. Изображение автора.

Это и есть «диапазон разлива». E9 содержит фактическую формулу, а F9 и G9 заполняются сами, без собственных формул. 

Щёлкните F9 — в строке формул вы увидите «засеренную» исходную формулу, а не что‑то введённое в F9 напрямую. Попробуйте ввести что‑то в F9, пока разлив из E9 активен, — Excel выдаст ошибку разлива. 

Как это исправить, рассмотрим в разделе «Распространённые ошибки».

Как обрабатывать отсутствующие значения в XLOOKUP()

По умолчанию XLOOKUP() выдаёт ошибку #N/A, когда не может найти совпадение. Аргумент if_not_found позволяет вместо стандартной ошибки показать то, что нам нужно.  

Предположим, в C9 я ввожу Product ID, которого нет в моих данных, например «P999».

Возврат пользовательского сообщения

В G2 я ввожу:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Поскольку «P999» отсутствует в столбце A, XLOOKUP() полностью пропускает ошибку и возвращает «Not Found». Можно заменить фразу на «Check Product ID» или «No Match».

Вывод настраиваемого сообщения вместо стандартной ошибки. Изображение автора.

Возврат пустой ячейки

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

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Это удобно, когда формула используется в отчёте, где лишние «Not Found» выглядели бы неаккуратно рядом со столбцом реальных значений.

Показывать пустую ячейку вместо стандартной ошибки. Изображение автора.

Оставить как реальную ошибку

Если полностью пропустить if_not_found, XLOOKUP() вернёт #N/A. Это не всегда плохо. Если вы создаёте формулу для себя и хотите сразу отлавливать опечатки или пропуски, всплывающий #N/A полезнее, чем тихая пустая ячейка, скрывающая проблему.

Примечание: if_not_found перехватывает только отсутствие совпадений. Если, например, размеры lookup_array и return_array различаются, XLOOKUP() всё равно выдаст #VALUE!, и if_not_found это не остановит. Этот случай мы рассмотрим ниже в разделе «Распространённые ошибки».

Расширенные возможности XLOOKUP()

Рассмотрим продвинутые возможности XLOOKUP().

Поиск снизу вверх

Установите search_mode в -1, и XLOOKUP() будет просматривать диапазон снизу вверх, а не сверху вниз. Используйте это, когда в данных есть дубликаты, и вам нужна самая свежая запись, а не первая.

Допустим, в моём наборе данных три одинаковых Customer ID. В ячейке E2 я ввожу формулу по умолчанию, которая ищет сверху вниз: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Она вычисляется от начала таблицы и показывает результат первого совпадения. 

Но если я хочу искать снизу, использую формулу: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

В этой формуле -1 говорит Excel начинать поиск с конца набора данных. И будет возвращено 340 — это первое совпадение снизу.  

XLOOKUP() просматривает данные снизу вверх. Изображение автора.

Двоичный режим поиска

Установите search_mode в 2, и XLOOKUP() переключится с построчной проверки на двоичный поиск. 

Как это работает: 

• Сначала берётся элемент из середины диапазона. 

• Если это не совпадение, отбрасывается половина, где значения быть не может, затем берётся середина оставшейся части. 

• Так продолжается, пока не будет найдено совпадение, каждый раз деля данные пополам. 

• Задайте search_mode равным -2 — логика та же, только старт с последнего элемента вместо первого.

Здесь в E2 введите:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Эта формула возвращает 200 — совершенно другую строку.

Двоичный поиск даёт неверный результат. Изображение автора.

Потому что двоичный поиск работает только если ваш lookup_array отсортирован. У нас столбец Customer ID не отсортирован: идёт C100, C101, C100, C102, C100, и двоичный поиск отбрасывает неверную половину, выдавая неправильное совпадение. 

Теперь посмотрим, что будет, если данные действительно отсортировать. 

Отсортируйте тот же журнал по Customer ID по возрастанию (все три C100 в начале) и снова запустите ту же формулу. 

Двоичный режим поиска работает с отсортированными данными. Изображение автора.

Теперь E2 возвращает 120 — так же, как и обычный поиск. В этом и разница. Двоичный поиск пропускает только те данные, которые можно безопасно отбросить, и это верно, только если данные изначально отсортированы. 

Вложенные формулы XLOOKUP()

Вкладывайте один XLOOKUP() в другой, когда результат одного поиска должен подставляться во второй, вместо фиксированного диапазона.

Скажем, вы хотите найти цену, но из какого столбца её брать — зависит от отдельного поиска, например ценового уровня клиента.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

В этой формуле внутренний XLOOKUP() сначала определяет, из какого диапазона столбцов тянуть данные, исходя из D9, затем внешний XLOOKUP() использует этот результат как свой return_array. 

Вложенный XLOOKUP(). Изображение автора.

XLOOKUP() и VLOOKUP()

Мы подробно разобрали работу XLOOKUP(). Вот его сравнение с VLOOKUP().

Два момента, на которые стоит обратить внимание: 

• VLOOKUP() по умолчанию использует приблизительное совпадение. Если вы забудете четвёртый аргумент, ошибки не будет — он просто тихо вернёт неверное значение.

• Если вставить столбец на листе с VLOOKUP(), формулы незаметно начнут возвращать неправильное значение. XLOOKUP() избегает этого, ссылаясь на диапазоны напрямую, а не считая столбцы.

Это не делает VLOOKUP() устаревшим. Он по‑прежнему хорошо подходит для простых, статичных листов. Но для всего нового XLOOKUP() — более безопасный выбор.

XLOOKUP() и INDEX() с MATCH()

До появления XLOOKUP() функции INDEX() и MATCH() вместе были стандартным способом обойти ограничения VLOOKUP(). Используются две функции вместо одной. 

• MATCH() находит позицию искомого значения. 

• INDEX() затем использует эту позицию, чтобы вернуть результат из любого указанного места: слева или справа — неважно.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Сравните с версией на XLOOKUP(), которую мы использовали в этой статье:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() работает только в Microsoft 365 и Excel 2021 и новее, как мы упоминали ранее. Если книге нужно открываться в Excel 2019 или 2016, или вы делитесь с пользователями старых версий, INDEX() и MATCH() по‑прежнему работают везде.

Есть и более частный случай. 

INDEX() и MATCH() разделяют «найти позицию» и «вернуть значение», что помогает в некоторых сложных вложенных формулах. Такое бывает нечасто, большинству читателей это не понадобится.

Вне этих двух случаев XLOOKUP() — лучший выбор для новой книги. 

Распространённые ошибки XLOOKUP() и как их исправлять

Краткая справка по наиболее вероятным проблемам и способам их устранения.

Ошибки #N/A

Это стандартный ответ XLOOKUP(), когда совпадение не найдено. С этим мы уже сталкивались ранее, когда lookup_value и lookup_array не совпадали, и XLOOKUP() было не с чем сопоставлять в столбце A.

Как исправить: убедитесь, что ваш lookup_value действительно существует в lookup_array, с тем же написанием и форматом. Если отсутствие совпадений ожидаемо и нормально, используйте аргумент if_not_found, чтобы заменить #N/A на что‑то более полезное, например «Not Found» или пустую ячейку, как мы обсуждали ранее. 

Неверный диапазон поиска

Это происходит, когда lookup_array и return_array не согласованы: неверные строки, столбцы или несовпадающие по размеру диапазоны. 

В зависимости от версии Excel или Sheets это может проявляться как ошибка #VALUE! или как #N/A с отдельным сообщением, объясняющим реальную проблему. Не дайте #N/A ввести вас в заблуждение, будто это отсутствие совпадения. 

Как исправить: проверьте, чтобы lookup_array и return_array охватывали одинаковое количество строк или столбцов. Если lookup_array — A2:A5, то return_array тоже должен охватывать строки 2–5, а не 4–5 или любой другой несовпадающий диапазон.

Несоответствие типов данных

VLOOKUP() может выдать ошибку, если ожидал число, а получил текст. У XLOOKUP() может возникнуть похожая ситуация. Если ваш lookup_value — число, а в lookup_array те же значения хранятся как текст (или наоборот), XLOOKUP() не найдёт совпадение, хотя на экране значения выглядят одинаково.

Как исправить: проверьте, одинаковы ли типы данных у lookup_value и lookup_array. Числа, сохранённые как текст, обычно выравниваются по левому краю, а не по правому. Оберните lookup_value в VALUE(), чтобы преобразовать текст в число, или используйте TEXT() для обратного преобразования — в зависимости от направления несоответствия.

Ошибки разлива

Это случается, когда ваш return_array охватывает более одного столбца, но ячейки, куда XLOOKUP() должен «разлиться», заняты. Excel нужен свободный простор рядом с ячейкой формулы для разлива результатов. Если там что‑то есть, формула целиком падает.

Как исправить: очистите ячейки, куда должны попадать результаты, затем введите формулу. Если эти ячейки нужны для другого, переместите формулу XLOOKUP() в другое место с свободным пространством вокруг.

Заключение 

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

В дальнейшем используйте XLOOKUP() по умолчанию для любых новых книг. Он гибче, чем VLOOKUP(), проще для чтения, чем INDEX/MATCH(), и избавляет от необходимости оборачивать каждую формулу в IFERROR().

Если хотите копнуть глубже, полезно отдельно изучить и INDEX(), и MATCH().