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

До сих пор XLOOKUP возвращал одно значение. Но функция также может сразу возвращать целую строку или столбец данных.

Когда формула возвращает несколько значений, они автоматически разворачиваются в соседние ячейки. Благодаря этому один XLOOKUP может заполнить целую небольшую запись.

Секрет в том, чтобы охватить возвращаемым массивом несколько столбцов. Вместо возврата только B2:B20 укажите B2:E20.

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

Введите формулу в одну ячейку, например F2, и нажмите Enter. Значения появятся в ячейках F2, G2, H2 и I2.

Тонкая синяя рамка обозначает диапазон развёртывания. Изменять нужно только левую верхнюю ячейку; остальные заполняются автоматически и не могут быть изменены напрямую.

В таблице сотрудников в столбце A находятся ID, а в столбцах B–E — имя, отдел, должность и зарплата.

Введите ID в D2, и один XLOOKUP вернёт всю запись целиком. Измените ID — и вся строка мгновенно обновится. Небольшой инструмент поиска в одной формуле.

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

Здесь XLOOKUP ищет в строке заголовков B1:E1 метку из D2 и разворачивает вниз весь столбец B2:E50 под соответствующим заголовком.

После развёртывания формулы можно сослаться на весь развёрнутый диапазон, добавив к ячейке символ решётки, например F2#.

Это очень удобно: SUM по развёрнутой строке остаётся правильной, даже если ширина строки изменится, потому что F2# всегда означает «весь диапазон, развёрнутый из F2».

Поскольку результат является массивом, его можно напрямую передавать функциям, которые принимают диапазоны.

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

Формуле с развёртыванием нужны пустые ячейки для заполнения. Если на пути развёртывания уже содержатся данные, XLOOKUP возвращает ошибку #SPILL!.

Исправить это просто: очистите блокирующие ячейки или переместите формулу в свободную область. Диапазон развёртывания должен быть полностью пустым.

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

Разместите один XLOOKUP, возвращающий строку данных, а выше него укажите ссылку на диапазон заголовков. Когда диапазон развёртывания расширяется или сужается, подписи по-прежнему выравниваются с возвращёнными столбцами.

Можно даже вложить один XLOOKUP в другой. Внутренний возвращает целый столбец, а внешний выбирает из него одну ячейку.

Так получается настоящий двусторонний поиск — одновременно по строке и столбцу — только с помощью XLOOKUP. Это удобная альтернатива INDEX-MATCH-MATCH.

Теперь Вы знаете, что XLOOKUP может возвращать несколько значений:

• Массив из нескольких столбцов разворачивает целую строку
• Массив из нескольких строк разворачивает целый столбец
• На развёрнутый диапазон можно ссылаться с помощью суффикса #
• Чтобы избежать #SPILL!, очистите блокирующие ячейки

Так одна формула превращается в полноценное средство просмотра записей.

Проверьте, насколько Вы поняли развёртывание результатов XLOOKUP.

Вы завершили изучение XLOOKUP и научились разворачивать результаты:

• Расширяйте возвращаемый массив, чтобы развернуть целую строку или столбец
• Развёрнутые результаты заполняют пустые соседние ячейки и обозначаются синей рамкой
• Ссылайтесь на развёрнутый диапазон с помощью суффикса #, например F2#
• Не допускайте #SPILL!, оставляя область развёртывания пустой

Освоив синтаксис, резервные значения, направления поиска и развёртывание, Вы сможете заменить XLOOKUP почти все устаревшие поисковые формулы.