Генератор формул ИНДЕКС ПОИСКПОЗ (INDEX MATCH)

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

Каждая формула и каждый результат проверки создаются в вашем браузере. Диапазоны, значения и ячейки примера, которые вы вводите, никогда не загружаются, и ничего не отправляется в ArrayKit.

Создаёт и поддерживает Udit Agarwal

Открыть генератор формул ВПР (VLOOKUP)

Об инструменте ИНДЕКС ПОИСКПОЗ

INDEX MATCH — это поиск, который умеет то, чего не может VLOOKUP: он ищет по любому столбцу, возвращает любой другой, переживает вставку столбцов и масштабируется на несколько условий. При этом легко ошибиться, ведь диапазон возврата и диапазон поиска должны совпадать по расположению, а форма с несколькими условиями требует работы с массивами. В этом генераторе пять режимов. Базовый режим и поиск влево записывают классическую пару. Двусторонний режим использует один MATCH для строки и другой для столбца. Режим нескольких условий предлагает форму с массивом, вариант, которому не нужно Ctrl+Shift+Enter, вспомогательный столбец и альтернативы на FILTER или XLOOKUP. Режим N-го совпадения возвращает второе или третье вхождение. Пример таблицы локально вычисляет результат, а сравнительная таблица объясняет, когда лучше выбрать INDEX MATCH, VLOOKUP или XLOOKUP. Ничего из введённого не загружается.

Возможности

  • Пять режимов: базовый, поиск влево, двусторонний, несколько условий и N-е совпадение
  • Типы совпадения: точное, по возрастанию (1) и по убыванию (-1), с предупреждениями о сортировке данных
  • От одного до четырёх условий: как формула массива, версия без массива, вспомогательный столбец, XLOOKUP и FILTER
  • Предупреждает, если диапазоны возврата и поиска начинаются в разных строках или различаются по высоте
  • Обёртка IFERROR или IFNA, TRIM, преобразование текста и чисел и подстановочные знаки
  • Разделитель аргументов — запятая или точка с запятой — для Excel и Google Таблиц в любой локали
  • Живая таблица-пример, подсвечивающая найденную и возвращаемую ячейки
  • Сравнение INDEX MATCH, VLOOKUP и XLOOKUP бок о бок

Как использовать ИНДЕКС ПОИСКПОЗ

  1. Выберите режим: базовый, поиск влево, двусторонний, несколько условий или N-е совпадение
  2. Введите искомое значение и диапазоны и закрепите их кнопкой со знаком доллара
  3. Выберите обёртку, обрезку пробелов и разделитель, подходящие вашей таблице
  4. Скопируйте основную формулу или одну из альтернатив для других версий Excel или Google Таблиц
  5. Отредактируйте пример таблицы, чтобы подтвердить результат, а затем прочитайте предупреждения, если что-то выглядит неверно

Пример

Ввод

Mode:        Multiple criteria
Return:      $D$2:$D$100  (Sales)
Criterion 1: $A$2:$A$100 = F2  (South)
Criterion 2: $B$2:$B$100 = G2  (Apple)

Результат

=IFERROR(INDEX($D$2:$D$100,MATCH(1,INDEX(($A$2:$A$100=F2)*($B$2:$B$100=G2),0),0)),"Not found")

Live test: South + Apple returns 95 (the first matching row)

Дополнительный INDEX(...,0) избавляет от необходимости нажимать Ctrl+Shift+Enter в старых версиях Excel.

Частые ошибки и устранение неполадок

  • INDEX MATCH возвращает значение из не той строки. — Диапазон возврата и диапазон поиска начинаются в разных строках. INDEX берёт позицию, найденную MATCH, поэтому оба диапазона должны начинаться в одной строке и иметь одинаковую высоту. Генератор предупреждает, если это не так.
  • Формула с несколькими условиями показывает #VALUE! или только первую ячейку в старом Excel. — Классической форме с массивом до Excel 365 нужно Ctrl+Shift+Enter. Используйте версию без массива с INDEX(...,0), вспомогательный столбец или XLOOKUP — все они работают с обычным Enter.
  • MATCH возвращает #N/A для значения, которое явно есть в списке. — Проверьте, нет ли чисел, хранящихся как текст, и пробелов в конце. Включите TRIM и подходящее преобразование числа или текста и оставьте тип совпадения точным (0), если список не отсортирован.
  • Формула N-го совпадения пуста или выдаёт ошибку, когда n слишком велико. — Совпадений меньше, чем запрошенное число. Оберните формулу в IFERROR; IFNA не перехватывает ошибку, которую даёт этот случай.

Часто задаваемые вопросы

Как генератор формул ИНДЕКС ПОИСКПОЗ записывает поиск влево?
Он ставит возвращаемый столбец первым в INDEX, а столбец, по которому ищете, — в MATCH, так что ответ может находиться левее столбца поиска. VLOOKUP так не умеет, потому что возвращает только столбцы справа от своего первого.
Чем отличаются формулы с массивом и без массива для нескольких условий?
Обе перемножают проверки ИСТИНА/ЛОЖЬ и ищут число 1. Классической форме нужно Ctrl+Shift+Enter в Excel 2019 и более ранних, а версия с INDEX(...,0) внутри MATCH принудительно вычисляет массив, так что обычный Enter работает в любой версии Excel.
Как двусторонний режим генератора ИНДЕКС ПОИСКПОЗ находит ячейку?
Один MATCH находит позицию строки среди ваших подписей строк, а второй — позицию столбца среди заголовков. INDEX затем возвращает ячейку на пересечении этой строки и столбца диапазона данных, получается поиск по двум ключам.
Какой тип совпадения выбрать в генераторе ИНДЕКС ПОИСКПОЗ?
Точное (0) — для ID, имён и кодов. По возрастанию (1) — для наибольшего значения, не превышающего искомое, в отсортированных данных, например налоговых диапазонах, а по убыванию (-1) — для наименьшего значения, не меньшего искомого, в данных, отсортированных от большего к меньшему.
Может ли генератор ИНДЕКС ПОИСКПОЗ вернуть второе или третье совпадение?
Да. Режим N-го совпадения строит вариант с AGGREGATE для Excel 2010 и новее, классический вариант с массивом и вариант с INDEX FILTER для Excel 365 и Google Таблиц. Число n задаётся числом или ссылкой на ячейку.
Остаются ли данные моей таблицы приватными при использовании генератора ИНДЕКС ПОИСКПОЗ?
Да. Диапазоны, искомые значения и ячейки примера остаются в вашем браузере и вычисляются на вашем устройстве. Последний режим и настройки, а также ячейки тестовой сетки сохраняются в локальном хранилище этого браузера, чтобы страница открывалась так, как вы её оставили.

Связанные инструменты

  • Генератор формул ВПР — Пошагово составьте формулу ВПР с IFERROR, точным или приблизительным поиском, XLOOKUP и INDEX MATCH и живой проверкой.
  • Генератор формул Excel — Выберите задачу — условные суммы, поиск, даты, текст или ранжирование — и получите готовую формулу Excel или Таблиц.
  • Объяснение формул Excel — Вставьте формулу Excel или Google Таблиц и получите пошаговый разбор каждой функции и аргумента простым языком.
  • Очистка данных Excel — Уберите пробелы, пустые строки и дубликаты, выровняйте регистр, даты и числа и сохраните в .xlsx или CSV.
  • Конвертер Excel to CSV — Экспортирует лист .xlsx или .xls в чистый файл CSV
  • CSV в таблицу Markdown — Превратите CSV или TSV в таблицу Markdown в стиле GitHub.

Другие инструменты: Excel и таблицы

  • Объяснение формул Excel — Вставьте формулу Excel или Google Таблиц и получите пошаговый разбор каждой функции и аргумента простым языком.
  • Генератор формул Excel — Выберите задачу — условные суммы, поиск, даты, текст или ранжирование — и получите готовую формулу Excel или Таблиц.
  • Переводчик функций Excel — Переводите формулы Excel между английским, немецким, французским, испанским, русским и другими языками, с разделителями.
  • Генератор макросов VBA — Выберите из 50+ макросов, задайте листы и диапазоны и скопируйте аккуратный код VBA с обработчиком ошибок.
  • CSV в Excel — Превратите CSV или TSV в оформленную книгу .xlsx: разделители, заголовки, типы столбцов и несколько листов.
  • JSON в Excel — Конвертируйте массивы и вложенные объекты JSON в книгу Excel с развёрнутыми столбцами и листом на массив.
  • Очистка данных Excel — Уберите пробелы, пустые строки и дубликаты, выровняйте регистр, даты и числа и сохраните в .xlsx или CSV.
  • Генератор формул ВПР — Пошагово составьте формулу ВПР с IFERROR, точным или приблизительным поиском, XLOOKUP и INDEX MATCH и живой проверкой.

Все инструменты: Excel и таблицы (9) · Все инструменты