ПРОСМОТР (LOOKUP), да не X

Несколько слов о старой функции LOOKUP / ПРОСМОТР. Все сказанное актуально и для Google Таблиц, и для Excel (и для отечественного Р7, где есть и старая ПРОСМОТР, и новая ПРОСМОТРX). Скриншоты сделаны в Excel.

Функция внешне похожа на новую XLOOKUP / ПРОСМОТРX. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы (она требует постоянной сортировки данных, не особо подходит для поиска текста), хотя она и используется иногда в составе формул массива - эта функция умеет работать с массивами без Ctrl+Shift+Enter даже в старых версиях (пример будет ниже) - как СУММПРОИЗВ / SUMPRODUCT.

ПРОСМОТР решает задачу по интервальному поиску, как ВПР / VLOOKUP (по умолчанию, когда четвертый аргумент пропущен) - когда нужно определить, в какой интервал попадает число:

=ПРОСМОТР(что ищем; где ищем; откуда возвращаем результат)

В отличие от ВПР, в случае с ПРОСМОТРом результат может быть в другой строке:

Конечно, с ВПР такое тоже можно провернуть, но не из коробки: сначала придется собрать данные в один виртуальный массив с помощью фигурных скобок или HSTACK

ПРОСМОТР может работать с горизонтальными массивами (ВПР, например, не умеет, это делает схожая функция ГПР / HLOOKUP):

У ПРОСМОТРа есть еще одна форма, когда аргументов два, а не три. В таком случае поиск ведется в первом столбце/первой строке массива (второго аргумента), а значение возвращается из последнего столбца (строки) массива:

=ПРОСМОТР(что ищем; массив)

Для поиска текста (то, что делает ПРОСМОТРХ / XLOOKUP по умолчанию и ВПР с последним аргументом, равным нулю или FALSE/ЛОЖЬ) ПРОСМОТР не очень хорош, мягко говоря. Он будет работать, опять-таки, только при сортировке данных (ключевой столбец - тот, в котором мы ищем - должен быть отсортирован по возрастанию). То есть мы всегда должны поддерживать сортировку (по алфавиту в случае с текстом)!

Да, кстати, ПРОСМОТР не учитывает регистр, как и другие функции поиска.

Но это еще полбеды. Если у вас будут отсортированные данные, то все будет работать. Но при несовпадении (достаточно даже лишнего пробела) в ключе для поиска ПРОСМОТР не будет сигнализировать ошибкой #Н/Д / #N/A), КАК ПОИСКПОЗ / MATCH, ПОИСКПОЗX / MATCHX, ПРОСМОТРХ / XLOOKUP, ВПР / VLOOKUP и ГПР / HLOOKUP. Она принесет неверные данные!

Итого. Не лучшая это функция для поиска текста. Используйте ВПР / VLOOKUP или ИНДЕКС+ПОИСКПОЗ / INDEX+MATCH везде (эти варианты требуют сортировки только при поиске ближайших чисел), в Excel 2021 и Google Таблицах можно пользоваться новой функцией ПРОСМОТРX / XLOOKUP (работает без сортировки даже с числами).

Но, как я писал выше, бывают прикольные примеры использования ПРОСМОТРа. Вот парочка.

Поиск последнего значения в столбце

Эту задачу решает вот такая загадочная формула:

=ПРОСМОТР(2;1/(столбец<>""); столбец)

Что тут происходит?

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

В Excel 2021 такое можно прямо в ячейках, в прошлых версиях можно выделить кусочек формулы в ней самой и нажать F9, чтобы полюбоваться на возвращаемый массив значений

Дальше мы делим единицу на каждое значение в этом массиве. Получаем единицу и ошибки (из-за деления на ноль):

Во дела...

И в этом массиве (выше он выведен для демонстрации, а вообще он существует виртуально внутри функции, конечно) мы ищем двойку (или другое число, которое в нем априори не появится), а возвращаем значение из самого столбца. Двойку мы не найдем и ПРОСМОТР в итоге найдет последнее числовое значение и вернет соответствующее ему значение из столбца A.

Нечеткий текстовый поиск

Пример от Николая Павлова - из его замечательной книги "Мастер формул" (там еще есть мощные примеры применения ПРОСМОТРа).

Допустим, у вас кривоватый список названий, и нужно его исправить. Компания "Дичайший Лемур" может быть записана и как "Лемур", и как "Дикий Лемур", и как-то еще. Если находим слово "Лемур" - мы должны возвращать соответствующее этому слову полное название компании. Слова для поиска и полные названия у нас в отдельной табличке.

Будем искать ключевые слова функцией ПОИСК / SEARCH в названии компании:

ПОИСК(список слов для поиска;название)

Допустим, у нас список слов для поиска такой:

А название очередной компании — "ИП Барсик".

Такая конструкция выдаст массив вида:

{#ЗНАЧ!:4:#ЗНАЧ!}

Потому что "Лемура" и "Котозавра" не найдет, а "Барсик" в названии на 4 позиции.

Далее мы ПОИСК засунем в ПРОСМОТР и будем искать в этом массиве число - допустим, 32768 (потому что это максимально возможное число знаков в ячейке, больше не понадобится). Ближайшее к нему - это 4 в нашем случае, так что ПРОСМОТР выдаст второе по порядку значение из массива результатов - "ИП Барсик".

=ПРОСМОТР(32768;
ПОИСК(слова для поиска;текст, в котором ищем);
список для подстановки)