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

Одно из главных ограничений VLOOKUP заключалось в том, что функция могла возвращать значения только справа от столбца поиска. У XLOOKUP такого ограничения нет.

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

Предположим, ID находятся в столбце B, а имена — в столбце A, то есть слева. VLOOKUP не могла работать так без перестановки столбцов.

С XLOOKUP достаточно выполнить поиск в столбце B и вернуть значения из столбца A. Функция без проблем возвращает значение, расположенное слева от столбца поиска.

У XLOOKUP есть необязательный пятый аргумент — search_mode, который задаёт направление поиска.

• 1 — поиск от первого элемента к последнему (по умолчанию)
• -1 — поиск от последнего элемента к первому
• 2 — двоичный поиск в данных, отсортированных по возрастанию
• -2 — двоичный поиск в данных, отсортированных по убыванию

По умолчанию XLOOKUP ищет сверху вниз и возвращает первое найденное совпадение.

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

Установите режим поиска -1, чтобы искать снизу вверх. Тогда XLOOKUP вернёт последнее совпадающее значение.

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

Представьте журнал, в котором каждая строка содержит обновление статуса заказа, а самые старые записи находятся вверху.

Поиск сверху вниз возвращает первый статус заказа. Поиск снизу вверх с помощью -1 возвращает его последний статус. Одни и те же данные дают два совершенно разных результата в зависимости от направления поиска.

Аргументы имеют позиционный порядок, поэтому нельзя перейти сразу к шестому аргументу, не указав предыдущие.

В формуле =XLOOKUP(D2, A2:A20, B2:B20, "Not found", 0, -1) значение "Not found" — это аргумент «если не найдено», а 0 — режим сопоставления (точное совпадение). Они занимают соответствующие позиции, чтобы -1 оказался в режиме поиска.

Соседний с пятым аргументом режим сопоставления определяет, как выполняется сопоставление:

• 0 — точное совпадение (по умолчанию)
• -1 — точное совпадение или ближайшее меньшее значение
• 1 — точное совпадение или ближайшее большее значение
• 2 — сопоставление с подстановочными символами * и ?

Для поиска слева и снизу обычно оставляют значение 0.

В таблице изменений цен каждый товар записан при каждом обновлении его цены. Чтобы получить текущую цену, нужно найти последнюю запись для этого товара.

Поиск снизу с помощью -1 возвращает самую свежую цену без сортировки и дополнительных вспомогательных столбцов.

Режимы поиска 2 и -2 используют более быстрый двоичный поиск, но требуют сортировки массива поиска: по возрастанию для 2 и по убыванию для -2.

В огромных отсортированных списках такой поиск значительно быстрее. Если данные на самом деле не отсортированы, двоичный поиск может вернуть неправильный результат, поэтому используйте его осторожно.

Теперь Вы управляете и тем, где, и тем, в каком направлении выполняется поиск XLOOKUP:

• Свободно возвращайте значения слева
• 1 — первое совпадение (по умолчанию)
• -1 — последнее совпадение
• 2 или -2 — быстрый двоичный поиск в отсортированных данных

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

Проверьте, насколько Вы поняли аргумент режима поиска.

Вы узнали о возможностях XLOOKUP в разных направлениях:

• Возвращаемые массивы могут находиться слева от массива поиска
• Режим поиска (шестой аргумент) задаёт направление
• -1 находит последнее совпадение, что идеально подходит для поиска последней записи
• 2 и -2 включают быстрый двоичный поиск в отсортированных данных

Далее Вы научитесь возвращать целые строки или столбцы с помощью одного XLOOKUP.