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 Таблиц

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

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

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

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

=ИНДЕКС(A1:C10, 7, 1)
Используйте ИНДЕКС для Google Таблиц, чтобы получить запись из определенной ячейки.

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

=ИНДЕКС(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. Вуаля!
    Используйте Google Sheets INDEX MATCH для поиска значений.
  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 может и действительно смотрит слева от столбца поиска. Функция ВПР всегда ищет самый первый столбец диапазона и ищет совпадения справа от него — в противном случае она получает только ошибки #Н/Д:
    Посмотрите налево с помощью Google Sheets INDEX MATCH.
  2. Никаких перепутанных ссылок при добавлении новых столбцов и перемещении существующих. Если вы добавите или переместите столбцы, ПОИСКПОЗ ИНДЕКС автоматически отразит изменения, не вмешиваясь в результат. Поскольку вы используете ссылки на столбцы, они мгновенно настраиваются Google Sheets:
    Посмотрите, как корректируются формулы, не испортив результат.

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

  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)))
Используйте функцию EXACT для построения ИНДЕКС-ПОИСКПОЗ с учетом регистра.

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

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

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

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

=Формуламассива(ИНДЕКС(B2:B24, ПОИСКПОЗ(СЦЕПИТЬ(F2:F4), A2:A24&C2:C24&D2:D24, 0)))
Как использовать INDEX MATCH в Google Таблицах с несколькими критериями.

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

  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. Его учебная страница подробно объяснит каждый вариант.

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

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

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *