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

Последний аргумент VLOOKUP — поиск по диапазону — определяет поведение поиска. Он небольшой, но очень важный:

• FALSE (или 0) означает точное совпадение
• TRUE (или 1) означает приближённое совпадение

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

При точном совпадении находится значение, полностью совпадающее с искомым. Если такого значения нет, VLOOKUP возвращает ошибку #N/A, а не пытается угадать.

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

Для точного совпадения таблицу не нужно сортировать. VLOOKUP просматривает её, пока не найдёт нужное значение.

В нашей таблице цен (A100 Яблоко 0.50, B200 Банан 0.30, C300 Вишня 1.20) точный поиск существующего кода работает безупречно:

Возвращается значение 1.20. Но если выполнить поиск по несуществующему коду, например "Z999", вы получите #N/A. Эта ошибка на самом деле полезна: она сообщает, что элемент действительно отсутствует, вместо того чтобы вернуть неверное соседнее значение.

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

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

С TRUE связано одно важное правило, которое рассматривается в следующем эпизоде.

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

Если столбец не отсортирован, TRUE возвращает непредсказуемые неверные результаты без какой-либо ошибки. Именно этот незаметный сбой заставляет многих избегать TRUE, если им специально не нужно распределение по диапазонам.

Представьте таблицу оценок в диапазоне A1:B5, отсортированную по возрастанию минимального балла:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

Для балла 76 должна возвращаться оценка C, потому что 76 попадает в диапазон 70–79. В формуле используется TRUE, чтобы найти наибольший порог, не превышающий 76:

При искомом значении 76 и TRUE VLOOKUP проходит по отсортированным порогам: 0, 60, 70, 90... Он сравнивает каждый из них.

• 0 меньше или равно 76: продолжаем
• 60 меньше или равно 76: продолжаем
• 70 меньше или равно 76: продолжаем
• 90 больше 76: останавливаемся

Затем он возвращается к последней подходящей строке (70) и возвращает соответствующий диапазон: C. Приближённый поиск фактически ищет значение между порогами.

Если полностью пропустить четвёртый аргумент, VLOOKUP по умолчанию использует TRUE — приближённое совпадение. Это удивляет многих, кто ожидает точного поиска.

Формула вроде =VLOOKUP(A2, Data!A:B, 2) для неотсортированного списка может незаметно вернуть неверное значение. Безопасное правило: всегда указывайте FALSE, если только вы намеренно не выполняете поиск по диапазонам в отсортированной таблице.

Вот основная разница:

• FALSE / точное: таблицу не нужно сортировать, для отсутствующего значения возвращается #N/A, лучше всего подходит для идентификаторов и кодов
• TRUE / приближённое: таблица должна быть отсортирована по возрастанию, для значений внутри диапазона #N/A не возвращается, лучше всего подходит для уровней и диапазонов

Выбирайте вариант в зависимости от вопроса, на который нужно ответить: «присутствует ли именно этот элемент?» — FALSE; «в какой диапазон попадает это значение?» — TRUE.

Для веса в D2 нужно определить стоимость доставки по отсортированной таблице диапазонов в A2:B6 (пороговые значения 0, 1, 5, 10, 20 кг). Приближённое совпадение выбирает нужный уровень:

Если в D2 указано 7, значение попадает в диапазон от 5 кг, и возвращается стоимость этого уровня. Измените вес — и уровень сразу обновится; перечислять каждый возможный вес не нужно.

Есть и ещё один важный аспект — производительность. В очень больших отсортированных таблицах приближённое совпадение (TRUE) может работать быстрее, поскольку электронная таблица может быстро переходить по отсортированным значениям, а не просматривать каждую строку.

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

Выберите подходящий тип совпадения для каждой ситуации.

Главные выводы:

• FALSE = точное совпадение, таблица может быть неотсортированной, для отсутствующих значений возвращается #N/A
• TRUE = приближённое совпадение, первый столбец должен быть отсортирован по возрастанию, находится наибольшее значение, не превышающее искомое
• Если не указать аргумент, по умолчанию используется TRUE, поэтому всегда указывайте его явно
• Используйте точное совпадение для идентификаторов и кодов, а приближённое — для уровней и диапазонов

Далее вы измените направление поиска и начнёте искать по строкам с помощью HLOOKUP.