Что лучше использовать вместо ВПР при работе с Гугл Таблицами/Экселем*

*Пост для тех, кто пользуется именно Гугл Таблицами/Экселем. Для бОльших объемов/скорости есть БД/Питон, сравнение на скрине №2.

ВПР (VLOOKUP) устарел, но многие все еще продолжают его использовать. Есть смысл переучить себя и перейти на другие функции — они быстрее и часто удобнее.

• Сравнение ВПР с аналогами

• ВПР
• + Простая, работает везде
• Только поиск вправо
• Скорость работы
• Чувствительна к изменениям структуры
• Нужна обертка для ошибок
• Возвращает только один результат
• XLOOKUP
• + Поиск в обе стороны
• + Обработка ошибок
• + Возврат массивов
• Только новые версии Excel + Google Sheets
• FILTER
• + Быстрая фильтрация по условиям
• + Возврат множества строк
• Требует современный Excel/Sheets
• QUERY
• + SQL-подобный синтаксис, мощная агрегация
• Только Google Sheets

Что быстрее?

Тесты на 500 000 строк показывают:

• ВПР (Excel): ~7,8 сек при правильном диапазоне, до 28,5 сек если указать целые столбцы вместо конкретного диапазона
• XLOOKUP (Excel): ~7,6 сек в базовом режиме, но с динамическими массивами может работать <1 сек — практически мгновенно
• FILTER (Excel): на современных версиях с динамическими массивами работает почти моментально, в разы быстрее классического ВПР
• QUERY (Google Sheets): сопоставим с FILTER по скорости, особенно эффективен для сложных выборок с несколькими условиями и агрегацией

Which to choose?

• Для Google Sheets: FILTER для большинства задач, QUERY для сложных выборок — динамические массивы (FILTER/XLOOKUP) выигрывают у ВПР в 5-10 раз
• Для Excel: XLOOKUP или INDEX+MATCH или join в Power Query

Если нужна скорость на очень больших объёмах — см. скрин 2

Примеры формул

Сделал таблицу с примером использования функций-заменителей, чтобы можно было легко попробовать.

https://docs.google.com/spreadsheets/d/1Ikir7VLkA9VfLLuN4CZCBP4QP0B_uBG2itaXygYqxus/edit?gid=0#gid=0