Функция XLOOKUP в Microsoft Excel знакома многим как удобный способ быстро найти нужное значение по строке или столбцу. Но мало кто знает: этот инструмент скрывает куда больший потенциал — с ним вы сможете делать двусторонний поиск, вытаскивая данные прямо на пересечении строки и столбца.
Если используете Excel на компьютере или Mac, убедитесь, что у вас версия не ниже Excel 2021 или активная подписка Microsoft 365 — только так XLOOKUP будет работать. Веб-версия, мобильные и планшетные приложения также поддерживают эту функцию.
Microsoft 365 Personal
Microsoft 365 открывает доступ ко всем приложениям Office — Word, Excel, PowerPoint и другим — сразу на пяти устройствах, а ещё к 1 ТБ облачного хранилища и целому ряду дополнительных фишек.
Как использовать XLOOKUP для поиска по одному критерию
Прежде чем перейти к двухстороннему поиску, нужно разобраться, как работает XLOOKUP в самом простом варианте — то есть по одной оси поиска.
Забудь про VLOOKUP: почему я перешёл на XLOOKUP
Старым методам — пора на свалку истории! Время перемен.
Синтаксис XLOOKUP включает шесть параметров:
Если аргумент d — что вывести при отсутствии совпадения — пропустить, XLOOKUP вернёт ноль. Аргументы e и f часто можно не указывать: Excel автоматически будет искать точное совпадение и двигаться сверху вниз или слева направо.
Например, если ввести вот такую формулу:
в ячейку G2, на выходе будет 48.
Что происходит: берётся ID из ячейки F2 (аргумент a), ищется в диапазоне A2:A100 (аргумент b), а возвращается значение из D2:D100 (аргумент c). Если ID не найден — появляется “Недопустимый ID” (аргумент d). Пятый параметр — точное совпадение (e), шестой — поиск сверху вниз (f).
Два критерия за один раз: как сделать двойной поиск в Excel с помощью XLOOKUP
Обычная формула XLOOKUP вытаскивает значение только по одному критерию — например, балл по ID. Но что если хочется узнать возраст или пол по тому же ID? Здесь выручает двойной (матричный) поиск: двухмерная формула, которая ищет на пересечении строки и столбца.
Вот тут и пригодятся вложенные XLOOKUP:
Разумеется, при необходимости оба XLOOKUP можно снабдить аргументами d, e, f, как в обычном варианте — для простоты здесь их пропустили.
Теперь, если ввести формулу в G2:
Excel снова покажет 48.
Как это работает: Excel сначала ищет нужный ID (аргумент a¹) в диапазоне A2:A100 (аргумент b¹), затем выбирает нужный параметр (аргумент a²) среди заголовков B1:D1 (аргумент b²) и выдаёт значение на их пересечении из диапазона B2:D100 (аргумент c).
Если сменить параметр в ячейке G1, допустим на "Возраст", результат изменится: будет 41.
Поменяйте заголовок на “Пол” — формула вернёт M.
Кстати, не важно, какая из XLOOKUP окажется внешней, а какая вложенной: результат будет тот же.
Лайфхак: подключите выпадающие списки к ячейкам поиска и забудьте про ручной ввод!
Когда двухсторонний поиск уже работает, добавьте себе удобства — сделайте для ячеек параметров выпадающие списки, чтобы выбирать нужное значение за пару кликов.
Как добавить выпадающий список в ячейку Excel
Больше не нужно вручную заносить одни и те же параметры по сто раз.
Для этого выделите, например, ячейку G1, перейдите на вкладку “Данные” и найдите опцию “Проверка данных”.
В появившемся окне выберите “Список” в поле “Тип данных”. Затем в качестве источника укажите диапазон, где записаны заголовки — обычно это B1:D1.
Нажмите “ОК” — и в G1 появится стрелочка: кликните по ней, и увидите весь перечень параметров.
Теперь то же самое можно сделать для ячейки F2 — очень удобно для быстрого выбора ID.
На заметку: диапазон лучше указывать с запасом, например, A2:A1000 — тогда новые ID будут появляться в списке автоматически. Специальный оператор после двоеточия позволяет игнорировать пустые строки в конце.
Можно тоже использовать формулу, чтобы меню само показывало только заполненные варианты.
Как создать выпадающий список на основе столбца в Excel
Выбор варианта зависит от того, как строится ваша таблица.
После этого можно выбирать нужный ID в F2 прямо из выпадающего списка.
Если же у вас Excel 2019 или старее — для двухстороннего поиска используйте связку INDEX и MATCH. XLOOKUP появился только в свежих версиях.
Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!
Премиум подписка - это доступ к эксклюзивным материалам, чтение канала без рекламы, возможность предлагать темы для статей и даже заказывать индивидуальные обзоры/исследования по своим запросам!Подробнее о том, какие преимущества вы получите с премиум подпиской, можно узнать здесь
Также подписывайтесь на нас в:
- Telegram: https://t.me/gergenshin
- Youtube: https://www.youtube.com/@gergenshin
- Яндекс Дзен: https://dzen.ru/gergen
- Официальный сайт: https://www-genshin.ru