что лучше и быстрее?

Погрузитесь в сравнение Excel: XLOOKUP и INDEX MATCH. Хотите знать, какая формула преобладает по скорости и эффективности? Раскройте секреты, узнайте плюсы и минусы и выберите лучшую формулу для ваших конкретных потребностей!

Одна из наиболее распространенных задач в Excel — поиск значений в таблице на основе некоторых критериев. Например, вам может потребоваться узнать цену продукта, имя клиента или сумму продаж за определенный месяц. Раньше пользователи Excel полагались на эту работу с помощью функции ВПР. Однако VLOOKUP имел ряд ограничений, что побудило опытных энтузиастов Excel разработать более отказоустойчивое решение с использованием комбинации INDEX MATCH. Теперь, с появлением более новых версий, у нас появилась функция XLOOKUP — более мощная и универсальная преемница VLOOKUP. Возникает вопрос: действительно ли XLOOKUP не только превосходит своего предшественника, но и превосходит проверенную комбинацию INDEX MATCH? Давайте углубимся в особенности и нюансы работы этих функций, чтобы это выяснить.

XLOOKUP vs INDEX MATCH – сравнение синтаксиса

Прежде всего, давайте разберем синтаксис этих формул и поймем, как они работают в Excel.

Формула INDEX MATCH – классический подход

Комбинация ИНДЕКС и ПОИСКПОЗ выдержала испытание временем как классический метод различных поисков в Excel. Синтаксис следующий:

ИНДЕКС (возвращаемый_массив, ПОИСКПОЗ (искомое_значение, искомый_массив, 0))

Здесь функция ПОИСКПОЗ определяет относительное положение искомого значения в подстановочном столбце, а ИНДЕКС извлекает соответствующее значение из возвращаемого столбца в этой позиции. Третий аргумент функции ПОИСКПОЗ (тип_соответствия), равный 0, обеспечивает точное совпадение, что обычно является желаемым поведением.

Хотя эта формула включает в себя две отдельные функции, она обеспечивает большую гибкость при обработке различных сценариев поиска.

Дополнительные сведения см. в разделе Формула ИНДЕКС и ПОИСКПОЗ в Excel.

Формула INDEX XMATCH – улучшенная версия

Функция XMATCH, модернизированная альтернатива ПОИСКПОЗ, была представлена ​​одновременно с XLOOKUP и доступна только в Excel 365 и Excel 2021. Вот ключевые улучшения:

• В отличие от ПОИСКПОЗ, XMATCH по умолчанию использует точное совпадение, что соответствует большинству вариантов использования.
• XMATCH не требует сортировки массива поиска для приблизительного совпадения.
• В отличие от своего предшественника, XMATCH может выполнять поиск в обратном порядке и выполнять двоичный поиск, оптимизированный по скорости. Это поведение контролируется необязательным аргументом search_mode.

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

ИНДЕКС (возвращаемый_массив, XMATCH (искомое_значение, искомый_массив, [match_mode], [search_mode])

Короче говоря, INDEX XMATCH — это более продвинутый способ выполнения тех же действий, что и INDEX MATCH, который позволяет решать более сложные и разнообразные задачи поиска. В этом уроке мы будем рассматривать эти формулы как по существу взаимозаменяемые, признавая, что каждая из них имеет свои сильные стороны и может быть предпочтительнее в разных контекстах.

Функция XLOOKUP – современное решение

Функция XLOOKUP, часто называемая «убийцей» VLOOKUP, представляет собой современный подход к операциям поиска. Он доступен только для подписчиков Office 365 и пользователей Excel 2021, предлагая мощную альтернативу. Синтаксис XLOOKUP следующий:

XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])

Преодолевая многие ограничения VLOOKUP, XLOOKUP может обрабатывать широкий спектр сценариев, включая поиск слева, горизонтальный и вертикальный поиск, двусторонний поиск и многое другое. Его универсальность делает его идеальным выбором для пользователей, которым нужен комплексный и эффективный метод поиска в последних версиях Excel.

Дополнительные сведения см. в разделе Excel XLOOKUP с примерами формул.

Разница между XLOOKUP и INDEX MATCH

Выбор способа поиска данных в Excel может существенно повлиять на гладкость вашей работы. Давайте рассмотрим различия, чтобы вы могли сделать лучший выбор в соответствии с вашими конкретными требованиями и типом данных, с которыми вы работаете.

Вот некоторые ключевые различия, которые следует учитывать:

Совместимость. Формула ИНДЕКС-ПОИСКПОЗ хорошо работает в любой версии Excel, что делает ее надежным и широко используемым вариантом. Функция XLOOKUP доступна только для пользователей Excel 365 и Excel 2021; он не имеет обратной совместимости со старыми версиями.

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

Числовой индекс и диапазон возврата. ИНДЕКС ПОИСКПОЗ опирается на числовой индекс, который представляет позиции значений, которые вы хотите найти и вернуть. Функция ПОИСКПОЗ находит позицию искомого значения, а ИНДЕКС возвращает значение в этой позиции. Этим числовым индексом можно легко манипулировать для различных целей, например, для возврата значений из разных столбцов ваших данных. С другой стороны, XLOOKUP оперирует ссылкой на возвращаемый массив, что является еще одним способом выполнения аналогичных действий.

Соответствие. Традиционная формула INDEX MATCH позволяет найти точные совпадения в любом наборе данных, а также приблизительные совпадения (ближайшие меньшие или самые большие), но только если ваши данные отсортированы. XLOOKUP и INDEX XMATCH могут выполнять любое сопоставление несортированных данных благодаря встроенным функциям.

Обратный поиск. В то время как INDEX MATCH может выполнять поиск только от начала до конца, XLOOKUP и INDEX + XMATCH могут выполнять поиск в обоих направлениях: от первого к последнему и от последнего к первому.

Бинарный поиск. Еще одним преимуществом XLOOKUP и INDEX XMATCH является то, что их можно настроить на использование двоичного поиска, который очень быстр и эффективен для отсортированных данных. Бинарный поиск работает путем деления диапазона пополам и проверки того, находится ли значение в левой или правой части. Затем он повторяет процесс, пока не найдет значение или не достигнет конца. Таким образом, он может очень быстро искать большие объемы данных.

Обработка ошибок. Комбинация INDEX MATCH не имеет встроенной функции обработки ошибок. Если формула не может найти искомое значение, отображается #Н/Д. С другой стороны, у XLOOKUP есть дополнительный аргумент (if_not_found), который позволяет настроить ответ в случае отсутствия совпадения. Вы можете указать определенное значение, отобразить дружелюбное сообщение или даже выполнить другую формулу.

Как использовать XLOOKUP вместо INDEX MATCH

А теперь давайте обсудим практические примеры, чтобы увидеть, как XLOOKUP может заменить старомодную формулу INDEX MATCH для решения различных проблем поиска в Excel.

Основная формула

Чтобы проиллюстрировать переход, давайте рассмотрим типичный пример, когда у вас есть список имен студентов в одном столбце, а соответствующие им баллы на экзаменах — в другом. В этом сценарии вы хотите получить оценку конкретного учащегося, скажем, из ячейки E4.

Формула ИНДЕКС-ПОИСКПОЗ для этой задачи будет выглядеть следующим образом:

=ИНДЕКС(B4:B25, ПОИСКПОЗ(E4, A4:A25, 0))

XLOOKUP предоставляет столь же простое решение — просто укажите необходимое искомое значение, диапазон поиска и диапазон возврата:

=XПРОМ(E4, A4:A25, B4:B25)

Как вы можете видеть на скриншоте, обе формулы дают одинаковый результат:

Левый поиск

Как вы, вероятно, знаете, одним из самых больших ограничений VLOOKUP является его неспособность возвращать значения из столбцов, расположенных слева от столбца поиска.

И XLOOKUP, и INDEX MATCH превосходно справляются с задачами поиска слева, обеспечивая решение, которого не хватает VLOOKUP. Вот как вы можете выполнить поиск слева с помощью каждого метода:

=ИНДЕКС(A4:A25, ПОИСКПОЗ(E4, B4:B25, 0))

=XПРОМ(E4, B4:B25, A4:A25)

Обе формулы ищут указанное значение (E4) в столбце B и возвращают соответствующее значение из столбца A.

Горизонтальный поиск

В более старых версиях Excel функция HLOOKUP была специально разработана для горизонтального поиска в строках. Хорошей новостью является то, что и XLOOKUP, и INDEX MATCH одинаково хорошо справляются с горизонтальным поиском. Чтобы добиться этого, вам просто нужно ориентировать поиск и возвращать массивы в строках, а не в столбцах.

=ИНДЕКС(B4:J4, ПОИСКПОЗ(B8, B3:J3, 0))

=XПРОМ(B8, B3:J3, B4:J4)

Обработка целых столбцов и строк

При настройке реальных листов пользователи обычно ссылаются на целые столбцы или строки в своих формулах, чтобы учесть потенциальные дополнения данных в будущем. И XLOOKUP, и INDEX MATCH легко поддерживают этот подход. Например:

=ИНДЕКС(A:A, ПОИСКПОЗ(E4, B:B, 0))

=XLOOKUP(E4, B:B, A:A)

Предупреждение для тех, кто хочет использовать этот метод: ссылки на целые столбцы или строки вынуждают Excel обрабатывать очень большие массивы. Это может повлиять на производительность и замедлить работу ваших листов. Вы всегда должны думать о том, как это может повлиять на скорость вычислений, и стараться ограничить диапазоны данных, если производительность является проблемой.

Искать в обратном порядке

В сценариях, когда вам нужно искать данные от конца к началу (снизу вверх для вертикального поиска или справа налево для горизонтального поиска), могут помочь XLOOKUP или INDEX XMATCH. Стоит отметить, что функция ПОИСКПОЗ лишена этой возможности и не может быть применена в таких ситуациях.

Чтобы выполнить поиск от последнего к первому с помощью XLOOKUP, вы устанавливаете необязательный аргумент search_mode равным -1:

=XLOOKUP(F4, B4:B24, C4:C24, , ,-1)

Аналогично, для обратного поиска с использованием INDEX XMATCH:

=ИНДЕКС(C4:C24, XMATCH(F4, B4:B24, ,-1))

Двусторонний поиск

Двусторонний поиск, также известный как «матричный» или «двумерный поиск», — это метод, который находит значение на пересечении определенной строки и столбца. Комбинация ИНДЕКС-ПОИСКПОЗ хорошо подходит для такого типа поиска, поскольку ИНДЕКС может ориентироваться как на номера строк, так и на номера столбцов, и для этой цели вы можете использовать две разные функции ПОИСКПОЗ.

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

=ИНДЕКС(B4:D25, ПОИСКПОЗ(G4, A4:A25, 0), ПОИСКПОЗ(G5, B3:D3, 0))

Теперь, когда дело доходит до 2D-поиска с помощью XLOOKUP, он становится немного сложнее, поскольку в первую очередь предназначен для одного поискового массива. Чтобы одновременно обрабатывать столбцы и строки, вы вкладываете одну функцию XLOOKUP в другую. Внутренняя функция ищет свое значение и возвращает столбец или строку связанных данных. Затем этот массив становится return_array для внешней функции. Вот как это выглядит:

=XПРОМ(G4, A4:A25, XПРОМ(G5, B3:D3, B4:D25))

Дополнительные примеры формул см. в разделе «Как выполнить двусторонний поиск в Excel».

Поиск по нескольким критериям

Структура обеих формул позволяет довольно легко применять несколько критериев. Общий подход следующий: сначала вы тестируете каждое условие индивидуально, генерируя временные массивы поиска значений TRUE и FALSE. Затем умножьте элементы этих массивов, чтобы создать единый массив поиска, где 1 представляет совпадение, а 0 — отсутствие совпадения. Затем вы устанавливаете для параметра search_value значение 1, чтобы получить первое найденное совпадение.

Например, чтобы получить дату данного экзамена (критерии 1 – F5) для конкретного студента (критерии 2 – F4), формулы будут выглядеть следующим образом:

=ИНДЕКС(A4:A24, ПОИСКПОЗ(1, (B4:B24=F4) * (C4:C24=F5), 0))

=XLOOKUP(1, (B4:B24 = F4) * (C4:C24 = F5), A4:A24)

В Excel 2019 и более ранних версиях, которые не поддерживают динамические массивы, вам необходимо ввести ИНДЕКС ПОИСКПОЗ как формулу массива, одновременно нажав клавиши Ctrl + Shift + Enter. В противном случае он не будет работать корректно. Формула ИНДЕКС ПОИСКПОЗ, не являющаяся массивом, с несколькими условиями также существует, но имеет немного более сложный синтаксис.

Для более глубокого понимания и практических примеров обратитесь к следующим руководствам:

Возвращаемые значения из нескольких столбцов

С помощью XLOOKUP получить информацию из нескольких соседних столбцов очень просто, если эти столбцы соседствуют друг с другом. Просто выберите диапазон, включающий все нужные вам столбцы, и используйте его в качестве возвращаемого массива.

Например, вот как вы можете получить результаты экзамена из столбцов B, C и D для определенного студента в ячейке G4:

=XПРОМ(G4, A4:A25, B4:D25)

Дуэт INDEX MATCH идет немного дальше, извлекая данные из любых столбцов, даже из тех, которые не находятся рядом друг с другом:

=ИНДЕКС(B4:D25, ПОИСКПОЗ(G4, A4:A25, 0), ПОИСКПОЗ(F8:G8, B3:D3, 0))

Проще говоря, эта формула использует двумерный подход INDEX MATCH. Первое ПОИСКПОЗ находит номер правой строки для имени учащегося в G4, а второе ПОИСКПОЗ определяет номера столбцов для предметов в F8:G8.

Обратите внимание, что в Excel 2019 и более ранних версиях это работает только как формула массива CSE (для правильного расчета необходимо нажать Ctrl + Shift + Enter).

Чтобы вернуть значения из несмежных столбцов с помощью XLOOKUP, вы можете использовать его вместе с функцией FILTER. Горизонтальный массив типа {1,0,1} добавлен в включать Аргумент FILTER определяет, какие столбцы возвращать (1), а какие отфильтровывать (0).

=XLOOKUP(G4, A4:A25, ФИЛЬТР(B4:D25, {1,0,1}))

Обработка пропущенных значений

Если указанное значение не найдено, базовая форма формулы ИНДЕКС и ПОИСКПОЗ возвращает ошибку #Н/Д. Однако вы можете решить эту проблему, обернув формулу в функцию ЕСЛИОШИБКА или ЕСНА и предоставив собственное сообщение.

Например, чтобы заменить ошибку #N/A собственным текстом, вы можете использовать INDEX MATCH вместе с IFNA:

=IFNA(ИНДЕКС(B4:B25, MATCH(E4, A4:A25, 0)), «Не найдено»)

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

=XLOOKUP(E4, A4:A25, B4:B25, «Не найдено»)

Обе формулы дают один и тот же результат, позволяя корректно обрабатывать пропущенные значения.

В заключение отметим, что и XLOOKUP, и INDEX MATCH — это мощные и удобные функции для выполнения различных задач поиска в Excel. В зависимости от вашей ситуации вы можете предпочесть один другому. Однако всегда полезно знать оба метода и знать, как их эффективно использовать. В конечном итоге выбор за вами.