Один раз опишите поиск — и получите правильную формулу ВПР (VLOOKUP) для Excel или Google Таблиц с вариантами IFERROR, XLOOKUP и INDEX MATCH и живой проверкой, всё в вашем браузере.
Генератор формул ВПР (VLOOKUP)
Один раз опишите поиск — и получите правильную формулу ВПР (VLOOKUP) для Excel или Google Таблиц с вариантами IFERROR, XLOOKUP и INDEX MATCH и живой проверкой, всё в вашем браузере.
Конструктор формул и живая проверка работают целиком в вашем браузере. Искомые значения, диапазоны и ячейки примера, которые вы вводите, никогда не загружаются, и ничего не отправляется в ArrayKit.
Большинство неработающих формул ВПР (VLOOKUP) ломаются по одним и тем же причинам: диапазон не закреплён, индекс столбца сдвинут на единицу, приблизительное совпадение работает по несортированным данным или число сравнивается с текстом. Этот генератор запрашивает искомое значение, диапазон таблицы и возвращаемый столбец, а затем записывает формулу с уже правильным порядком аргументов и кавычками. Введите букву столбца вместо номера — и она будет пересчитана относительно начала таблицы. Одним щелчком добавьте IFERROR или IFNA, TRIM, преобразование текста в число или обратно либо подстановочный знак, а разделитель аргументов переключите на точку с запятой для европейских локалей. Под формулой небольшая редактируемая таблица вычисляет её по настоящим правилам VLOOKUP и честно показывает #N/A. Всё считается на вашем устройстве; ни ячейки, ни формулы не загружаются.
Возможности
Записывает VLOOKUP с правильным порядком аргументов, кавычками и удвоенными кавычками внутри текста
Возвращаемый столбец по номеру или по букве, пересчитанный относительно первого столбца таблицы
Закрепление знаком $ одним щелчком, диапазоны целых столбцов и ограниченный диапазон со строки 2 по 100
Обёртка IFERROR или IFNA с вашим текстом «не найдено»
Параметры TRIM, текст как число, число как текст и подстановочный знак для неаккуратных искомых значений
Равнозначные формулы XLOOKUP, INDEX MATCH, FILTER и QUERY, у каждой своя кнопка «Копировать»
Разделители-запятая или точка с запятой для Excel и Таблиц в любой локали
Живая тестовая сетка с поведением точного и приблизительного поиска, подстановочных знаков и несортированных данных плюс чек-лист по #N/A
Как использовать Генератор формул ВПР
Введите искомое значение как ячейку, например F2, либо наберите текст или число для поиска
Введите диапазон таблицы с искомым столбцом первым, затем закрепите его кнопкой со знаком доллара
Выберите возвращаемый столбец по номеру или букве и укажите точное или приблизительное совпадение
При необходимости добавьте IFERROR, TRIM или смените разделитель, затем скопируйте формулу
Проверьте результат на примере таблицы и прочитайте чек-лист, если он показывает #N/A
Пример
Ввод
Lookup value: F2 (the cell contains 103)
Table range: $A$2:$D$100
Return column: 3 (Price)
Match type: Exact
If not found: IFERROR with "Not found"
Результат
=IFERROR(VLOOKUP(F2,$A$2:$D$100,3,FALSE),"Not found")
XLOOKUP: =IFERROR(XLOOKUP(F2,$A$2:$A$100,$C$2:$C$100),"Not found")
INDEX MATCH: =IFERROR(INDEX($C$2:$C$100,MATCH(F2,$A$2:$A$100,0)),"Not found")
Live test on the sample table: 103 returns 4.00
Один и тот же поиск, записанный тремя способами, со строкой cherry, подсвеченной в живой проверке.
Частые ошибки и устранение неполадок
VLOOKUP возвращает #N/A, хотя значение явно есть в таблице. — Скорее всего, у значений разные типы или скрытые пробелы. Число, хранящееся как текст, никогда не равно настоящему числу, а пробел в конце даёт другое значение. Включите параметр TRIM или преобразуйте одну сторону параметрами «число» и «текст».
Формула работает в первой строке и ломается ниже. — Диапазон таблицы не был закреплён и сдвигался при протягивании формулы. Нажмите кнопку «Закрепить $», чтобы диапазон выглядел как $A$2:$D$100, и протяните формулу снова.
VLOOKUP возвращает не ту строку в несортированной таблице. — Приблизительное совпадение предполагает, что первый столбец отсортирован по возрастанию, и выполняет двоичный поиск. Переключитесь на точное совпадение для ID, имён и кодов, а приблизительное оставьте для диапазонов, например налоговых ставок.
Excel сообщает #REF! после изменения диапазона таблицы. — Индекс столбца больше числа столбцов в диапазоне. Генератор проверяет это и сообщает ширину таблицы до того, как вы скопируете формулу.
Часто задаваемые вопросы
Как генератор формул ВПР превращает букву столбца в индекс?
Он вычитает первый столбец диапазона таблицы из введённой буквы и прибавляет единицу. Если таблица начинается со столбца B, возвращаемый столбец D получает индекс 3. Если буква левее таблицы, инструмент объясняет, что VLOOKUP не умеет искать влево, и предлагает INDEX MATCH.
Что на самом деле вычисляет живая проверка в генераторе формул ВПР?
Она применяет параметры вашей формулы к небольшой таблице на странице по тем же правилам, что и электронная таблица: точное совпадение игнорирует регистр и учитывает подстановочные знаки, приблизительное — это двоичный поиск, ожидающий сортированных данных, пустые возвращаемые ячейки дают ноль, а несовпадение типов даёт #N/A.
Что выбрать для обёртки ВПР: IFERROR или IFNA?
IFNA скрывает только ошибку «не найдено», поэтому неверный индекс столбца или сломанный диапазон всё равно видны. IFERROR скрывает любые ошибки — удобно для отчётов, но может замаскировать ошибки. Генератор предлагает оба и показывает результат каждого в живой проверке.
Когда безопасно использовать приблизительное совпадение в ВПР?
Только когда первый столбец отсортирован по возрастанию, а вам нужно ближайшее значение, не превышающее искомое, например в таблице «балл — оценка». Генератор предупреждает об этом, а тестовая сетка отмечает несортированные данные, ведь тогда ответ может оказаться неверным без всякой ошибки.
Почему под моим ВПР появляются версии XLOOKUP и INDEX MATCH?
Они возвращают ту же ячейку при тех же входных данных, но обходят ограничения VLOOKUP: умеют искать влево и не ломаются при вставке столбца. Скопируйте ту, что подходит версии Excel или Google Таблиц у ваших коллег.
Отправляет ли генератор формул ВПР мою таблицу или формулу куда-либо?
Нет. Текст формулы и ячейки примера обрабатываются только в вашем браузере. Последние настройки и ячейки тестовой сетки запоминаются в локальном хранилище на вашем устройстве, чтобы страница открывалась так, как вы её оставили.
Связанные инструменты
ИНДЕКС ПОИСКПОЗ — Формулы INDEX MATCH для поиска влево, двустороннего поиска и нескольких условий с живой проверкой и готовым результатом.
Генератор формул Excel — Выберите задачу — условные суммы, поиск, даты, текст или ранжирование — и получите готовую формулу Excel или Таблиц.
Объяснение формул Excel — Вставьте формулу Excel или Google Таблиц и получите пошаговый разбор каждой функции и аргумента простым языком.
Очистка данных Excel — Уберите пробелы, пустые строки и дубликаты, выровняйте регистр, даты и числа и сохраните в .xlsx или CSV.