INDEX MATCH в Google Sheets — еще один способ вертикального поиска

Когда вам нужно найти данные в вашем листе, которые соответствуют определенной ключевой записи, обычно вы обращаетесь к Google Sheets VLOOKUP. Но вот: ВПР почти сразу накладывает на вас ограничения. Вот почему вам лучше увеличить ресурсы для задачи, изучив ПОИСКПОЗ ИНДЕКС.

ИНДЕКС ПОИСКПОЗ в Google Sheets представляет собой комбинацию двух функций: ИНДЕКС и ПОИСКПОЗ. При использовании в тандеме они служат лучшей альтернативой Google Sheets VLOOKUP. Давайте узнаем их возможности вместе в этом блоге. Но сначала я хотел бы кратко рассказать вам об их собственных ролях в электронных таблицах.

Функция ПОИСКПОЗ в Google Таблицах

Я хотел бы начать с Google Sheets MATCH, потому что это очень просто. Он сканирует ваши данные для определенного значения и возвращает его позицию:

=ПОИСКПОЗ(search_key, диапазон, [search_type])

• search_key та запись, которую вы ищете. Необходимый.
• диапазон является строкой или столбцом для поиска. Обязательно.

  Примечание. ПОИСКПОЗ принимает только одномерные массивы: строку или столбец.

• search_type является необязательным и определяет, должно ли совпадение быть точным или приблизительным. Если опущено, по умолчанию равно 1:
  • 1 означает, что диапазон отсортирован в порядке возрастания. Функция получает наибольшее значение, меньшее или равное вашему search_key.
  • 0 заставит функцию искать точное совпадение, если ваш диапазон не отсортирован.
  • -1 намекает, что записи ранжируются с использованием сортировки по убыванию. В этом случае функция получает наименьшее значение, большее или равное вашему search_key.

Вот пример: чтобы получить позицию определенной ягоды в списке всех ягод, мне нужна следующая формула ПОИСКПОЗ в моих Google Таблицах:

=ПОИСКПОЗ(“Черника”, A1:A10, 0)

Функция ИНДЕКС Google Таблиц

В то время как ПОИСКПОЗ показывает, где искать ваше значение (его положение в диапазоне), функция ИНДЕКС Google Таблиц извлекает само значение на основе его смещения строки и столбца:

=ИНДЕКС(ссылка, [row], [column])

• ссылка это диапазон для поиска. Требуется.
• строка это количество строк для смещения от самой первой ячейки вашего диапазона. Необязательный, 0, если опущен.
• столбецкак строка, — количество смещенных столбцов. Также необязательно, также 0, если опущено.

Если вы укажете оба необязательных аргумента (строку и столбец), Google Sheets INDEX вернет запись из целевой ячейки:

=ИНДЕКС(A1:C10, 7, 1)

Пропустите один из этих аргументов, и функция получит всю строку или столбец соответственно:

=ИНДЕКС(A1:C10, 7)

Как использовать ИНДЕКС ПОИСКПОЗ в Google Таблицах — примеры формул

Когда ИНДЕКС и ПОИСКПОЗ используются вместе в электронных таблицах, они наиболее эффективны. Они могут полностью заменить Google Sheets VLOOKUP и получить необходимую запись из таблицы на основе значения вашего ключа.

Создайте свою первую формулу INDEX MATCH для Google Sheets

Предположим, вы хотите получить информацию о запасах клюквы из той же таблицы, которую я использовал выше. Я поменял местами только столбцы B и C (чуть позже вы узнаете почему).

1. Теперь все ягоды перечислены в столбце C. Функция ПОИСКПОЗ в Google Таблицах поможет вам найти точный ряд ягод клюквы: 8

   =ПОИСКПОЗ(“Клюква”, C1:C10, 0)

2. Поместите всю эту формулу ПОИСКПОЗ в строка аргумент в функции ИНДЕКС:

   =ИНДЕКС(A1:C10, ПОИСКПОЗ(“Клюква”, C1:C10, 0))

   Этот вернет всю строку с клюквой.

3. Но поскольку все, что вам нужно, это биржевая информация, укажите также номер столбца поиска: 3

   =ИНДЕКС(A1:C10, ПОИСКПОЗ(“Клюква”, C1:C10,0), 2)

4. Вуаля!
   
5. Вы можете пойти дальше и отказаться от этого последнего индикатора столбца (2). Вам это вообще не понадобится, если вы будете использовать только столбец подстановки (Б1:Б10), а не всю таблицу (А1:С10) в качестве первого аргумента:

   =ИНДЕКС(B1:B10, ПОИСКПОЗ(“Клюква”, C1:C10, 0))
   

   Кончик. Более удобным способом проверки наличия различных ягод было бы размещение их в выпадающем списке (Е2) и направьте свою функцию ПОИСКПОЗ на ячейку с этим списком:

   =ИНДЕКС(B1:B10, ПОИСКПОЗ(E2, C1:C10, 0))

   Как только вы выберете ягоду, соответствующее значение изменится соответствующим образом:
   

Почему INDEX MATCH в Google Sheets лучше, чем VLOOKUP

Вы уже знаете, что Google Sheets INDEX MATCH ищет ваше значение в таблице и возвращает другую связанную запись из той же строки. И вы знаете, что Google Sheets VLOOKUP делает то же самое. Так зачем беспокоиться?

Дело в том, что ПОИСКПОЗ ПО ИНДЕКСУ имеет ряд существенных преимуществ перед ВПР:

1. Возможен левосторонний поиск. Я изменил столбцы местами ранее, чтобы проиллюстрировать это: функция INDEX MATCH в Google Sheets может и действительно смотрит слева от столбца поиска. Функция ВПР всегда ищет самый первый столбец диапазона и ищет совпадения справа от него — в противном случае она получает только ошибки #Н/Д:
   
2. Никаких перепутанных ссылок при добавлении новых столбцов и перемещении существующих. Если вы добавите или переместите столбцы, ПОИСКПОЗ ИНДЕКС автоматически отразит изменения, не вмешиваясь в результат. Поскольку вы используете ссылки на столбцы, они мгновенно настраиваются Google Sheets:
   

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

3. При необходимости учитывает регистр текста (подробнее об этом справа ниже).
4. Может использоваться для вертикальный поиск по нескольким критериям.

Предлагаю вам подробно рассмотреть последние два пункта ниже.

V-поиск с учетом регистра с помощью INDEX MATCH в Google Sheets

ПОИСКПОЗ ПО ИНДЕКСУ — это то, что нужно, когда дело доходит до учета регистра.

Предположим, что все ягоды продаются двумя способами — навалом (взвешивание на прилавке) и упакованными в ящики. Следовательно, в списке есть два вхождения каждой ягоды, записанные в разных падежах, каждая со своим идентификатором, который также различается в падежах:

Итак, как вы можете найти информацию о запасах ягод, продаваемых определенным образом? ВПР вернет первое найденное имя независимо от регистра.

К счастью, INDEX MATCH для Google Sheets может сделать это правильно. Вам просто нужно будет использовать одну дополнительную функцию — НАЙТИ или ТОЧНО.

Пример 1. FIND для Vlookup с учетом регистра

НАЙТИ — это чувствительная к регистру функция в Google Таблицах, которая отлично подходит для вертикального поиска с учетом регистра:

=Формуламассива(ИНДЕКС(B2:B19, ПОИСКПОЗ(1, НАЙТИ(E2, C2:C19)), 0))

Давайте посмотрим, что происходит в этой формуле:

1. FIND просматривает столбец C (С2: С19) для записи от Е2 (вишня) с учетом регистра букв. После нахождения формула «отмечает» эту ячейку числом — 1.
2. ПОИСКПОЗ ищет эту метку — 1 — в том же столбце (С) и передает номер своей строки в INDEX.
3. ИНДЕКС сводится к этой строке в столбце B (Б2: Б19) и доставит вам нужную запись.
4. Когда вы закончите создание формулы, нажмите Ctrl+Shift+Enter, чтобы добавить ArrayFormula в начале. Это необходимо, потому что без этого FIND не сможет искать в массивах (более чем в одной ячейке). Или вы можете ввести ‘Формула массива‘ с вашей клавиатуры.

Пример 2. EXACT для Vlookup с учетом регистра

Если вы замените НАЙТИ на ТОЧНЫЙ, последний будет искать записи с точно такими же символами, включая их текстовый регистр.

Единственное отличие состоит в том, что EXACT “отмечает” совпадение с ПРАВДА а не число 1. Следовательно, первый аргумент для ПОИСКПОЗ должен быть ПРАВДА:

=Формуламассива(ИНДЕКС(B2:B19, ПОИСКПОЗ(ИСТИНА, ТОЧНОЕ(E2, C2:C19), 0)))

Google Sheets INDEX MATCH с несколькими критериями

Что делать, если есть несколько условий, на основании которых вы хотите получить запись?

Давайте проверим цену на вишня что продается в ПП ковши и уже убегать:

Я упорядочил все критерии в раскрывающихся списках в столбце F. И это Google Sheets INDEX MATCH, который поддерживает несколько критериев, а не VLOOKUP. Вот формула, которую вам нужно будет использовать:

=Формуламассива(ИНДЕКС(B2:B24, ПОИСКПОЗ(СЦЕПИТЬ(F2:F4), A2:A24&C2:C24&D2:D24, 0)))

Не паникуйте! 🙂 Его логика на самом деле довольно проста:

1. СЦЕПИТЬ (F2:F4) объединяет все три записи из ячеек с критериями в одну строку следующим образом:

   ВишняВедро из полипропиленаВыбегает

   Это search_key для ПОИСКПОЗ или, другими словами, то, что вы ищете в таблице.

2. A2:A24&C2:C24&D2:D24 представляют собой диапазон для функции ПОИСКПОЗ. Поскольку все три критерия находятся в трех отдельных столбцах, таким образом вы как бы объединяете их:

   ВишняКартонный лотокВ наличии
   ВишняПленочная упаковкаНет в наличии
   ВишняВедро из полипропиленаВыбегает
   и т.п.

3. Последний аргумент в ПОИСКПОЗ — 0 — позволяет найти точное совпадение для ВишняВедро из полипропиленаВыбегает среди всех этих рядов комбинированных столбцов. Как видите, он находится в 3-м ряду.
4. И тогда ИНДЕКС делает свое дело: он выбирает запись из 3-й строки столбца B.
5. ArrayFormula позволяет другим функциям работать с массивами.

Кончик. Если ваша формула не найдет соответствия, она вернет ошибку. Чтобы избежать этого, вы можете обернуть всю эту формулу в ЕСЛИОШИБКА (сделать ее первым аргументом) и ввести все, что вы хотите видеть в ячейке вместо ошибок, в качестве второго аргумента:

=ЕСЛИОШИБКА(Формуламассива(ИНДЕКС(B2:B27, ПОИСКПОЗ(СЦЕПИТЬ(F2:F4), A2:A27&C2:C27&D2:D27, 0)), “Не найдено”)

Лучшая альтернатива INDEX MATCH в Google Sheets — множественные совпадения с функцией VLOOKUP

Какую бы функцию поиска вы ни предпочли, ВПР или ПОИСКПОЗ ПО ИНДЕКСУ, есть лучшая альтернатива им обоим.

Несколько совпадений ВПР это специальное дополнение для Google Таблиц, предназначенное для:

• поиск без формул
• поиск по всем направлениям
• поиск по нескольким условиям для разных типов данных: текст, числа, даты, время, и т.п.
• получить несколько совпадений, столько, сколько вам нужно (конечно, при условии, что в вашей таблице их столько)

Интерфейс простой, так что сомневаться в том, что вы все делаете правильно, не придется:

1. Выберите исходный диапазон.
2. Установите количество совпадений и столбцов для возврата.
3. Настройте условия с помощью предопределенных операторов (содержит, =, не пусто, междутак далее.).

Вы также сможете:

• предварительно просмотреть результат
• решить, где разместить
• и как: формулой или просто значениями

Не упустите возможность проверить дополнение. Иди вперед и установите его из Google Workspace Marketplace. Его учебная страница подробно объяснит каждый вариант.

Также мы подготовили специальное обучающее видео:

До встречи в комментариях ниже или в следующей статье 😉

Вас также могут заинтересовать