Поиск последнего совпадающего значения

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

Когда список со временем растёт и один и тот же ключ встречается много раз, самая нижняя строка обычно содержит самые свежие данные. Обычная функция VLOOKUP или MATCH с точным совпадением вместо этого упорно выбирает верхнюю строку.

В этом уроке показано несколько надёжных способов получить последнее совпадающее значение.

MATCH(value, range, 0) просматривает данные сверху вниз и останавливается на самом первом точном совпадении. Если "Apple" встречается в строках 2, 5 и 9, MATCH возвращает 2.

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

Если у Вас современная версия Excel или Google Sheets, XLOOKUP значительно упрощает задачу. Его пятый и шестой аргументы управляют режимом совпадения и направлением поиска.

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

Здесь выполняется поиск товара из G1 в диапазоне A2:A10 и возвращается соответствующая цена из B2:B10; поиск начинается снизу.

В старых электронных таблицах широко известен приём, в котором LOOKUP используется вместе с числом 2 и хитрым делением на условие.

Выражение 1/(A2:A10=G1) даёт 1 для совпавших строк и ошибку деления для несовпавших. LOOKUP, выполняя поиск числа 2 — значения, превышающего все имеющиеся, — проходит мимо ошибок и останавливается на последней корректной 1, возвращая соответствующее значение из B2:B10.

Разберём 1/(A2:A10=G1) по шагам:

• В строках, где ключ совпадает, получается 1/TRUE = 1.
• В строках без совпадения получается 1/FALSE = ошибка #DIV/0!.

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

Можно также использовать семейство INDEX-MATCH. Сначала нужно найти позицию последнего совпадения, а затем передать её в INDEX.

Используйте тот же приём с делением внутри MATCH: выполните поиск числа 2 в выражении 1/(A2:A10=G1), чтобы получить позицию последнего совпадения. Затем передайте эту позицию в INDEX для столбца с возвращаемыми значениями.

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

Массив 1/(A2:A10=G1) содержит только 1 и ошибки. Наибольшее значение, не превышающее 2, равно 1, и MATCH возвращает позицию последней такой 1. Это и есть позиция последней совпавшей строки.

Предположим, в A2:A10 перечислены статусы заказа "Order-7", записанные с течением времени, а в B2:B10 находятся тексты статусов. "Order-7" встречается в строках 3, 6 и 9.

• Массив совпадений помечает строки 3, 6 и 9 единицами, а остальные — ошибками.
• MATCH(2, ...) возвращает 9 как позицию, отсчитываемую от начала диапазона, то есть последнее совпадение.
• INDEX возвращает статус из этой последней строки — самый новый статус.

Какой подход следует выбрать?

• XLOOKUP с -1: самый простой и понятный вариант, если приложение его поддерживает.
• LOOKUP(2, 1/...): работает почти везде и не требует специальной версии.
• INDEX-MATCH(2, 1/...): удобен, если Вам также нужна позиция или требуется вернуть значение из другого столбца.

Все три способа дают один и тот же результат; выбирайте метод с учётом доступных инструментов и желаемой понятности формулы.

Обратите внимание на следующие проблемы:

• Диапазоны разного размера: диапазон условий и диапазон возвращаемых значений должны иметь одинаковую высоту, иначе строки не совпадут.
• Скрытые дубликаты: пробелы в конце могут сделать "Apple " отличным от "Apple"; сначала очистите текст с помощью TRIM.
• Полное отсутствие совпадений: при отсутствии совпадений приём возвращает ошибку. Оберните его в IFERROR, чтобы вывести понятное сообщение.

Можно объединить приём поиска последнего совпадения с двумя условиями. Перемножьте проверки условий внутри деления, чтобы только строки, соответствующие обоим ключам, давали 1.

Например, найдите самую свежую цену, для которой товар равен G1 и регион равен G2. Приём LOOKUP(2, ...) по-прежнему остановится на последней подходящей строке.

Это удобно для журналов с отметками времени, где один и тот же товар встречается в нескольких регионах.

Чтобы вернуть последнее совпадающее значение вместо первого:

• Используйте XLOOKUP(..., -1) для поиска снизу вверх, если эта функция доступна.
• Используйте классический приём LOOKUP(2, 1/(range=key), result) в любой версии.
• Используйте INDEX(result, MATCH(2, 1/(range=key))), если Вам также нужна позиция.

Не забывайте использовать диапазоны одинакового размера, удалять лишние пробелы и для безопасности оборачивать формулу в IFERROR.