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

Быстрые ссылки

• Как работают функции INDEX и XMATCH в Excel

• Использование INDEX с XMATCH для однонаправленных поисков

• Использование INDEX с XMATCH для двусторонних поисков

Для многих комбинация INDEX и XMATCH — базовый инструмент для получения значения из таблицы. Однако тот же приём позволяет выполнять двусторонние поиски: возвращать значение на пересечении заданной строки и столбца. Это особенно полезно, когда вы хотите быстро переключать измерение (например, сотрудник + год) без редактирования формулы.

Статья рассчитана на читателей с любым уровнем владения Excel и постепенно усложняется. Если вы впервые видите INDEX и XMATCH — читайте по порядку. Если уже применяете их для однонаправленных поисков, пролистайте к разделу «Использование INDEX с XMATCH для двусторонних поисков».

Как работают INDEX и XMATCH

Прежде чем объединять эти функции в одну формулу, разберёмся, как они работают по отдельности.

Функция INDEX

Функция INDEX возвращает значение из диапазона по указанным номеру строки и номеру столбца. Синтаксис:

=INDEX(*a*,*b*,*c*)

где

• a — диапазон или таблица,
• b — номер строки внутри диапазона,
• c — номер столбца внутри диапазона.

Например, если ввести:

=INDEX(T_Profit,3,6)

в ячейку H2, формула вернёт значение, находящееся на пересечении 3-й строки и 6-го столбца таблицы T_Profit.

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

=INDEX(T_Profit,I2,I3)

в ячейке I5 возьмёт номер строки из I2 и номер столбца из I3, что делает формулу гибкой.

Важно: INDEX работает по относительным номерам внутри указанного диапазона или таблицы. Если вы используете структурированные ссылки (имена таблиц и заголовков), убедитесь, что понимаете структуру таблицы.

Функция XMATCH

XMATCH ищет элемент в одномерном диапазоне и возвращает позицию (порядковый номер) найденного элемента.

Функция доступна в Excel 2021 и в Excel для Microsoft 365, а также в веб-версии и мобильных приложениях Excel.

Microsoft 365 Personal

Microsoft 365 включает доступ к Office (Word, Excel, PowerPoint) на нескольких устройствах, 1 ТБ в OneDrive и другие сервисы.

$100 at Microsoft

$100 at Amazon

Вот синтаксис XMATCH:

=XMATCH(*a*,*b*,*c*,*d*)

где

• a — искомое значение,
• b — диапазон для поиска,
• c — тип сопоставления (0 = точное совпадение (по умолчанию); -1 = точное или ближайшее меньшее; 1 = точное или ближайшее большее; 2 = сопоставление с шаблонами, поддерживающее подстановочные символы),
• d — режим поиска (1 = сверху вниз (по умолчанию), -1 = снизу вверх, 2 = бинарный поиск при сортировке по возрастанию, -2 = бинарный поиск при сортировке по убыванию).

XMATCH — более современная замена MATCH. Основные преимущества: по умолчанию он ищет точное совпадение, умеет искать с конца списка и поддерживает подстановочные символы.

Пример:

=XMATCH(1927,T_Profit[Employee])

в ячейке H2 вернёт 3, потому что ID сотрудника 1927 находится третьим в столбце Employee таблицы T_Profit.

Если искомое значение хранится в ячейке, аргумент a может быть ссылкой на эту ячейку:

=XMATCH(I2,T_Profit[Employee])

Использование INDEX с XMATCH для однонаправленных поисков

Отдельно INDEX и XMATCH полезны, но сочетание раскрывает их потенциал. В одномерной задаче XMATCH возвращает номер строки, который затем передаётся в INDEX.

Предположим, вы хотите посмотреть суммарную прибыль, сгенерированную сотрудником, вводя его ID в ячейку I2.

В ячейку I5 введите формулу:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)

где

• T_Profit — имя таблицы, из которой извлекается значение,
• XMATCH(I2,T_Profit[Employee]) — возвращает номер строки для сотрудника, который указан в I2,
• 6 — номер столбца в таблице T_Profit, откуда берётся значение (например, столбец «Итого»).

Совет: вместо ввода структурированных ссылок вручную щёлкните по заголовку столбца — Excel автоматически вставит корректную ссылку.

В этом примере дополнительные аргументы XMATCH не указаны, потому что по умолчанию XMATCH ищет точное совпадение сверху вниз, что подходит в большинстве случаев.

Если нужно вернуть значение из другого столбца (например, прибыль за конкретный год), имеет смысл применить двусторонний поиск.

Использование INDEX с XMATCH для двусторонних поисков

Ключевая идея двустороннего поиска: один XMATCH находит номер строки по значению (например, ID сотрудника), второй XMATCH находит номер столбца по заголовку (например, год). Затем INDEX возвращает значение на пересечении.

Предположим, нужно узнать, сколько прибыли дал сотрудник 1191 в 2021 году. В ячейке I4 вводим:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))

где

• XMATCH(I2,T_Profit[Employee]) — номер строки (по сотруднику),
• XMATCH(I3,T_Profit[#Headers]) — номер столбца (по заголовку, например, «2021»).

Типичная ошибка: #N/A при числовых заголовках

Если заголовки столбцов выглядят как числа (например, 2020, 2021), формула может вернуть #N/A. Причина: Excel часто хранит заголовки таблицы как текст, даже если они выглядят как числа. Тогда сравнение «число vs текст» даёт несовпадение.

Решение шаг за шагом:

1. Выделите ячейку с искомым заголовком (в примере — I3).
2. На вкладке «Главная» откройте раскрывающийся список формата числа и выберите «Текст».
3. Нажмите F2, чтобы перейти в режим редактирования ячейки, и затем Enter — это пересохранит значение в новом формате.

После этого XMATCH увидит совпадение формата между искомым значением и заголовками таблицы, и INDEX вернёт ожидаемый результат.

Альтернативный приём, если вы не хотите менять формат ячейки: привести типы внутри формулы. Например, если заголовки — текст, а вводите число, используйте TEXT:

=INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(TEXT(I3,"0"),T_Profit[#Headers]))

Или наоборот, применяйте VALUE к заголовкам, если удобнее приводить их к числу.

Практический пример: менять параметры и видеть результат

Введите разные значения в I2 (ID сотрудника) и I3 (год/заголовок) — формула автоматически вернёт соответствующее значение без редактирования формулы.

Теперь, когда вы умеете выполнять двусторонние поиски, можно ускорить ввод параметров с помощью проверки данных (Data Validation) и выпадающих списков.

Важно: заголовки столбцов нельзя напрямую указать как источник списка для проверки данных, если они находятся в таблице — диалог Data Validation не принимает структурированные ссылки на заголовки. Обходные пути:

• Указать прямые ссылки на диапазон ячеек с заголовками (например, $B$1:$F$1),
• Или дать имя диапазону (Formulas → Define Name) и использовать имя в поле Source.

Советы и лучшие практики

• Всегда проверяйте формат данных при использовании XMATCH: «число» vs «текст» — частая причина ошибок.
• При работе с таблицами отдавайте предпочтение структурированным ссылкам (Table[Column]) — они надёжнее при изменении размеров таблицы.
• Используйте имена диапазонов для источников списков проверки данных — так легче поддерживать и переносить листы.
• Для динамических отчётов строьте интерфейс справа или сверху от таблицы: пользователю удобно менять параметры в отдельной панели.
• В больших таблицах подумайте о производительности: бинарный поиск (режим поиска 2 или -2) работает быстрее, но требует отсортированного диапазона.

Когда этот метод не подходит

• Если таблица не упорядочена и нужны складные условия (несколько критериев для строки и столбца одновременно), рассмотрите комбинацию FILTER или SUMIFS/INDEX с MATCH по нескольким измерениям.
• Если данные находятся в нескольких листах или книгах и требуется кросс-табличный агрегационный поиск, удобнее использовать Power Query или сводные таблицы.
• Для поиска с частичным совпадением по сложным шаблонам иногда проще применять XLOOKUP с подстановочными символами либо FILTER с SEARCH.

Альтернативные подходы

• XLOOKUP: удобен для односторонних поисков и имеет параметры обратного поиска и значения по умолчанию при отсутствии совпадения. Для двустороннего поиска XLOOKUP можно сочетать с TRANSPOSE или вторичным вызовом XLOOKUP.
• INDEX + MATCH: классический вариант (MATCH вместо XMATCH) — аналогичен, но MATCH менее гибок по умолчанию.
• VLOOKUP/HLOOKUP: устаревшие для двусторонних задач; неудобны при вставке/удалении столбцов.
• FILTER: когда требуется вернуть не одно значение, а массив строк или столбцов, удовлетворяющих критериям.

Мини‑методика внедрения (SOP)

1. Определите таблицу и её границы. Дайте таблице имя (Table → Table Name).

2. Создайте панель ввода параметров (например, ячейки I2 — ID, I3 — Заголовок).

3. Реализуйте формулу:

   • Однонаправленный пример:

     =INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),6)

   • Двусторонний пример:

     =INDEX(T_Profit,XMATCH(I2,T_Profit[Employee]),XMATCH(I3,T_Profit[#Headers]))

4. Протестируйте на 5–10 различных сочетаниях параметров.

5. Добавьте проверку данных для ввода параметров (Data Validation). Если нужно, создайте именованные диапазоны для списка заголовков.

6. Документируйте поведение формулы (где брать данные, что происходит при #N/A).

Роль‑ориентированные чек‑листы

Аналитик:

• Проверить, что таблица имеет правильные заголовки.
• Убедиться, что типы данных согласованы (текст/число).
• Настроить источник данных и объяснить логику формулы в комментариях.

BI‑разработчик:

• Оценить производительность при больших объёмах.
• Рассмотреть перенос агрегирования в Power Query или модель данных.
• Автоматизировать обновление именованных диапазонов.

Менеджер/Пользователь отчёта:

• Проверить удобство панели ввода параметров.
• Попросить добавить список выбора для снижения ошибок ввода.
• Убедиться, что поведение при отсутствии данных понятное (например, вернуть “—“ или “Нет данных”).

Критерии приёмки

• Для 10 тестовых сочетаний (ID + заголовок) формула возвращает ожидаемые значения.
• При вводе несуществующего сочетания формула возвращает определённую реакцию: #N/A или пользовательскую строку (при использовании IFERROR/XLOOKUP с аргументом по умолчанию).
• Компоненты отчёта (таблица, панель параметров, validation) сохраняют работоспособность при добавлении новых строк и столбцов в таблицу.

Примеры тестов (acceptance)

• Тест 1: Существующий ID и существующий заголовок → возвращает число > 0.
• Тест 2: Несуществующий ID → возвращает #N/A или кастомную ошибку.
• Тест 3: Заголовок в другом формате (число как значение) → формула корректно сопоставляет после приведения типов.
• Тест 4: Добавление новой строки в таблицу не ломает формулу и поиск по новому ID работает.

Однострочный глоссарий

• INDEX — возвращает значение по номеру строки и столбца внутри диапазона.
• XMATCH — возвращает позицию искомого элемента в одномерном диапазоне.
• Структированная ссылка — обращение к столбцу/таблице по имени (Table[Column]).

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

• Если формула возвращает #N/A — проверьте соответствие типов данных и наличие значения в диапазоне.
• Если результат неверный после вставки/удаления столбца — используйте структурированные ссылки, а не жёсткие номера столбцов.
• Для больших наборов данных тестируйте режимы поиска XMATCH: бинарный режим быстро работает только при отсортированном наборе.

Важно: всегда храните резервную копию листа перед массовыми правками форматов или структур таблицы.

Краткое резюме

INDEX + XMATCH — надёжный и гибкий приём для одно- и двусторонних поисков в Excel. Он позволяет строить динамичные отчёты, где пользователь меняет параметры, а формула моментально возвращает результат. Проблемы чаще связаны с типами данных и форматом заголовков — их легко решить приведением типов или корректной настройкой проверки данных.

Заметки:

• Для двусторонних поисков удобнее держать панель параметров рядом с таблицей.
• Для сложных многокритериальных задач рассмотрите FILTER и Power Query.

Краткое резюме: INDEX + XMATCH — это гибкость и точность в поиске значений по строкам и столбцам. Начните с простых примеров и добавляйте проверки данных и документацию по мере внедрения.