Поиск по столбцу
Почти каждый пользователь электронных таблиц рано или поздно сталкивается с задачей провэпээрить таблицу: найти значение в одном столбце и вернуть соответствующее значение из другого столбца. Типичный сценарий: перенести данные из одной таблицы в другую по какому-то идентификатору.
Функция VLOOKUP (ВПР) появилась в 1985 году в самой первой версии Excel и занимала третье место по популярности среди пользователей (после SUM и AVERAGE). За это время она морально устарела, поэтому в 2020 году разработчики Excel добавили новую функцию XLOOKUP. В 2022 году она появилась и в Google Sheets.
Чем же XLOOKUP лучше, чем VLOOKUP?
Напомню, VLOOKUP принимает на вход четыре параметра:
искомое значение;
ссылку на таблицу (поиск идёт по первому столбцу);
номер столбца с результатами;
тип поиска: точный или приблизительный.
1️⃣ VLOOKUP закладывается на структуру исходной таблицы. Если завтра порядок столбцов поменяется, формула может сломаться. Придётся руками обновлять номер столбца с результатами. XLOOKUP принимает на вход два диапазона и спокойно переживает перемещение любого из них:
=VLOOKUP("needle";A:Z;2;0)
=XLOOKUP("needle";A:A;B:B)
2️⃣ Для VLOOKUP столбец с результатами должен располагаться справа от столбца для поиска. Передать третьим аргументом отрицательное число нельзя. XLOOKUP лишён этого ограничения и позволяет доставать результаты слева от столбца для поиска:
=XLOOKUP("needle";B:B;A:A)
3️⃣ При неудачном поиске VLOOKUP возвращает #N/A. Если вместо ошибки хочется выводить что-то другое (например, пустое значение), приходится дополнительно вызывать функцию IFNA. В XLOOKUP можно четвёртым аргументом передать значение, которое будет выводиться при неудачном поиске:
=IFNA(VLOOKUP("needle";A:Z;2;0);"not found")
=XLOOKUP("needle";A:A;B:B;"not found")
4️⃣ По умолчанию VLOOKUP ищет приблизительное совпадение. Для поиска точного соответствия надо передать FALSE или ноль четвёртым параметром. Часто про это забывают и долго разбираются, почему функция работает не так, как ожидалось. XLOOKUP по умолчанию ищет точное соответствие, помогая избежать ошибок.
5️⃣ Приблизительный поиск VLOOKUP умеет искать только ближайшее меньшее значение. При этом исходная таблица должна быть отсортирована. XLOOKUP в режиме приблизительного поиска позволяет искать как меньшее, так и большее значение. Таблицу сортировать необязательно.
6️⃣ Если подходящих значений в таблице больше одного, VLOOKUP ищет только первое совпадение. XLOOKUP умеет запускать поиск с любого конца и может находить как первое, так и последнее совпадение.
Единственный минус XLOOKUP: функция недоступна в Excel 2019 и более ранних версиях. Да и по-русски называется ПРОСМОТРХ, где Х — это «икс», а не «ха». К вопросу, почему я избегаю русскоязычные названия функций.
