Как использовать новую и продвинутую функцию XLOOKUP в Excel (10 примеров)

Новая функция XLOOKUP в Excel — самая мощная и простая функция поиска, которую предлагает Excel. После долгих усилий Microsoft наконец выпустила её, чтобы заменить VLOOKUP, HLOOKUP, INDEX+MATCH и другие функции поиска.

В этом руководстве мы расскажем о преимуществах XLOOKUP, а также о том, как получить эту функцию и применять её для решения различных задач поиска.

Как получить XLOOKUP?

Синтаксис

Примеры

• Пример1: Точное совпадение
  • Выполнение точного совпадения с помощью XLOOKUP
  • Всего несколько кликов для получения точного совпадения

• Пример2: Приблизительное совпадение
  • Выполнение приблизительного совпадения с помощью XLOOKUP

• Пример3: Совпадение с использованием подстановочных знаков
  • Выполнение совпадения с подстановочными знаками с помощью XLOOKUP

• Пример4: Поиск справа налево
  • Поиск справа налево с помощью XLOOKUP
  • Поиск значения справа налево всего за несколько кликов

• Пример5: Вертикальный или горизонтальный поиск
  • Выполнение вертикального или горизонтального поиска с помощью XLOOKUP

• Пример6: Двусторонний поиск
  • Выполнение двустороннего поиска с помощью XLOOKUP

• Пример7: Настройка сообщения о ненайденном значении
  • Настройка сообщения о ненайденном значении с помощью XLOOKUP
  • Настройте ошибку #N/A с помощью удобной функции

• Пример8: Несколько значений
  • Возврат нескольких значений с помощью XLOOKUP

• Пример9: Несколько критериев
  • Выполнение многокритериального поиска с помощью XLOOKUP
  • Многокритериальный поиск быстрым способом

• Пример10: Поиск значения с последним совпадением
  • Получение последнего совпадающего результата с помощью XLOOKUP
  • Легко находите последнее совпадающее значение с помощью отличного инструмента

Скачать пример файла XLOOKUP

Как получить XLOOKUP?

Функция XLOOKUP доступна только в Excel для Microsoft365, Excel2021 и более новых версиях, а также в Excel для Интернета. Если вы используете Excel2019 или более раннюю версию, рекомендуется обновиться для доступа к XLOOKUP.

Синтаксис

Функция ищет диапазон или массив и возвращает значение первого совпадающего результата. Синтаксис следующий:

Аргументы:

1. Lookup_value (обязательный): значение, которое вы ищете. Оно может находиться в любом столбце диапазона table_array.
2. Lookup_array (обязательный): массив или диапазон, в котором вы ищете значение для поиска.
3. Return_array (обязательный): массив или диапазон, из которого вы хотите получить значение.
4. If_not_found (необязательно): значение, которое возвращается, если совпадение не найдено. Вы можете настроить текст в [if_not_found], чтобы показать, что совпадение отсутствует.
   В противном случае по умолчанию возвращается значение #N/A.
5. Match_mode (необязательно): здесь вы можете указать, как сопоставлять lookup_value со значениями в lookup_array.
   • 0 (по умолчанию) = Точное совпадение. Если совпадение не найдено, возвращается #N/A.
   • -1 = Точное совпадение. Если совпадение не найдено, возвращается следующее меньшее значение.
   • 1 = Точное совпадение. Если совпадение не найдено, возвращается следующее большее значение.
   • 2 = Частичное совпадение. Используйте подстановочные знаки *, ? и ~ для выполнения поиска с подстановкой.
6. Search_mode (необязательно): здесь вы можете указать порядок поиска.
   • 1 (по умолчанию) = Поиск значения для поиска от первого до последнего элемента в lookup_array.
   • -1 = Поиск значения для поиска от последнего к первому элементу. Это полезно, если нужно получить последний совпадающий результат в lookup_array.
   • 2 = Выполнить двоичный поиск, при этом lookup_array должен быть отсортирован по возрастанию. Если не отсортирован, результат будет недействительным.
   • -2 = Выполнить двоичный поиск, при этом lookup_array должен быть отсортирован по убыванию. Если не отсортирован, результат будет недействительным.

Для получения подробной информации об аргументах выполните следующие действия:

1. Введите приведённый ниже синтаксис в пустую ячейку, обратите внимание, что нужно ввести только одну сторону скобки.

2. Нажмите Ctrl+A — появится окно с аргументами функции. Вторая сторона скобки будет добавлена автоматически.

3. Разверните панель данных, чтобы увидеть все шесть аргументов функции XLOOKUP.

>>>

Примеры

Теперь вы наверняка освоили основные принципы XLOOKUP. Давайте перейдём к практическим примерам использования XLOOKUP.

Пример1: Точное совпадение

Выполнение точного совпадения с помощью XLOOKUP

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

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

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

=XLOOKUP(E2,A2:A10,C2:C10)

Теперь вы знаете цену за единицу для мыши с помощью продвинутой формулы XLOOKUP. Так как режим совпадения по умолчанию — точное совпадение, указывать его не нужно. Это гораздо проще и эффективнее, чем VLOOKUP.

Всего несколько кликов для получения точного совпадения

Возможно, вы используете более раннюю версию Excel и пока не планируете обновляться до Excel2021 или Microsoft365. В таком случае рекомендую удобную функцию — «Поиск данных в диапазоне». С её помощью вы получите результат без сложных формул и доступа к XLOOKUP.

1. Щёлкните по ячейке, в которую нужно поместить найденный результат.

2. Перейдите на вкладку «Kutools», выберите «Помощник формул», затем в выпадающем списке снова выберите «Помощник формул».

3. В диалоговом окне Помощник формул выполните следующие настройки:

• Выберите «Поиск» в разделе «Тип формулы»;
• В разделе «Выберите формулу» выберите «Поиск данных в диапазоне»;
• В разделе «Ввод аргумента» выполните следующие действия:
  • В поле «Диапазон таблицы» выберите диапазон данных, содержащий искомое значение и значение результата;
  • В поле «Значение для поиска» выберите ячейку или диапазон значения, которое вы ищете. Обратите внимание, оно должно быть в первом столбце диапазона таблицы;
  • В поле «Столбец» выберите столбец, из которого будет возвращено найденное значение.

4. Нажмите кнопку OK, чтобы получить результат.

Пример2. Приблизительное совпадение

Выполнение приблизительного совпадения с помощью XLOOKUP

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

В этом случае вам нужно узнать налоговые ставки для доходов сотрудников. Слева на листе приведены федеральные налоговые ставки на2021 год. Как узнать налоговую ставку для сотрудника из столбца E? Не переживайте, выполните следующие действия:

1. Введите приведённую ниже формулу в пустую ячейку E2 и нажмите Enter, чтобы получить результат.
Затем измените форматирование возвращённого результата по своему усмотрению.

=XLOOKUP(D2,B2:B8,A2:A8,,1)

>>>

√ Примечание: Четвёртый аргумент [If_not_found] необязателен, поэтому я его опустил.

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

• Дважды щёлкните по ячейке E2, чтобы отобразить формулу =XLOOKUP(D2,B2:B8,A2:A8,,1);
• Выделите диапазон поиска B2:B8 в формуле, нажмите F4, чтобы получить $B$2:$B$8;
• Выделите диапазон возврата A2:A8 в формуле, нажмите F4, чтобы получить $A$2:$A$8;
• Нажмите Enter, чтобы получить результат для ячейки E2.

>>>

3. Затем протяните маркер заполнения вниз, чтобы получить все результаты.

√ Примечание:

• Нажатие клавиши F4 на клавиатуре позволяет изменить ссылку на ячейку на абсолютную, добавив знаки доллара перед строкой и столбцом.
• После применения абсолютных ссылок к диапазонам поиска и возврата формула в ячейке E2 выглядит так:

=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)

• Когда вы протягиваете маркер заполнения вниз из ячейки E2, в каждой ячейке столбца E формула меняется только по значению для поиска.
  Например, формула в E13 теперь выглядит так:

=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)

Пример3: Совпадение с использованием подстановочных знаков

Выполнение совпадения с подстановочными знаками с помощью XLOOKUP

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

В Microsoft Excel подстановочные знаки — это специальные символы, которые могут заменять любые другие символы. Они особенно полезны при выполнении поиска по частичному совпадению.

Существует три типа подстановочных знаков: звёздочка (*), вопросительный знак (?) и тильда (~).

• Звёздочка (*) обозначает любое количество символов в тексте;
• Вопросительный знак (?) обозначает любой один символ в тексте;
• Тильда (~) используется для того, чтобы подстановочные знаки (*, ? ~) воспринимались как обычные символы. Поставьте тильду (~) перед подстановочным знаком, чтобы добиться этого эффекта;

В большинстве случаев при выполнении поиска с подстановочными знаками в XLOOKUP используется символ звёздочки (*). Теперь посмотрим, как работает совпадение с подстановочными знаками.

Предположим, у вас есть список рыночной капитализации50 крупнейших американских компаний, и вы хотите узнать капитализацию некоторых компаний, но их названия указаны сокращённо — это идеальный случай для поиска с подстановочными знаками. Следуйте пошаговой инструкции ниже.

√ Примечание: Чтобы выполнить совпадение с подстановочными знаками, самое главное — установить пятый аргумент [match_mode] в значение2.

1. Введите приведённую ниже формулу в пустую ячейку H3 и нажмите Enter, чтобы получить результат.

=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)

>>>

2. Теперь вы знаете результат для ячейки H3. Чтобы получить остальные результаты, закрепите lookup_array и return_array, установив курсор в массив и нажав клавишу F4. Тогда формула в H3 станет:

=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)

3. Протяните маркер заполнения вниз, чтобы получить все результаты.

√ Примечание:

• Значение для поиска в формуле в ячейке H3 — это "*"&G3&"*". Мы объединяем подстановочный знак звёздочка (*) со значением G3 с помощью амперсанда (&).
• Четвёртый аргумент [If_not_found] необязателен, поэтому я его опустил.

Пример4: Поиск слева

Поиск справа налево с помощью XLOOKUP

Один из недостатков VLOOKUP — он ограничен поиском только вправо от столбца поиска. Если попытаться искать значения слева от столбца поиска, будет возвращена ошибка #N/A. Не волнуйтесь, XLOOKUP идеально решает эту задачу.

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

Предположим, у вас есть список стран с телефонными кодами, и вы хотите найти название страны по известному телефонному коду.

Нам нужно искать в столбце C и возвращать значение из столбца A. Выполните следующие действия:

1. Введите приведённую ниже формулу в пустую ячейку G2.

=XLOOKUP(F2,C2:C11,A2:A11)

2. Нажмите Enter, чтобы получить результат.

√ Примечание: Функция поиска слева в XLOOKUP может заменить Index и Match для поиска значений слева.

Поиск значения справа налево всего за несколько кликов

Для тех, кто не хочет запоминать формулы, рекомендую полезную функцию — «Поиск справа налево». С её помощью вы сможете выполнить поиск справа налево всего за несколько секунд.

1. Перейдите на вкладку «Kutools» в Excel, найдите «Супер ПОИСК» и выберите «Поиск справа налево» в выпадающем списке.

2. В диалоговом окне «Поиск справа налево» выполните следующие настройки:

• В разделе «Диапазон значений для поиска и диапазон вывода» укажите диапазон поиска и диапазон вывода;
• В разделе «Диапазон данных» введите диапазон данных, затем укажите «ключевой столбец» и «столбец возврата»;

3. Нажмите кнопку OK, чтобы получить результат.

Пример5: Вертикальный или горизонтальный поиск

Выполнение вертикального или горизонтального поиска с помощью XLOOKUP

Как пользователи Excel, вы, вероятно, знакомы с функциями VLOOKUP и HLOOKUP. VLOOKUP ищет значения по вертикали в столбце, а HLOOKUP — по горизонтали в строке.

Теперь новая функция XLOOKUP объединяет оба варианта, то есть для вертикального или горизонтального поиска достаточно одного синтаксиса. Гениально, не так ли?

В приведённом ниже примере мы покажем, как с помощью одного синтаксиса XLOOKUP выполнять поиск по вертикали или горизонтали.

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

=XLOOKUP(E1,A2:A13,B2:B13)

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

=XLOOKUP(P1,B1:M1,B2:M2)

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

Пример6: Двусторонний поиск

Выполнение двустороннего поиска с помощью XLOOKUP

Вы всё ещё используете функции INDEX и MATCH для поиска значения в двумерной таблице? Попробуйте улучшенную функцию XLOOKUP — так задача решается гораздо проще.

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

Предположим, у вас есть список оценок студентов по разным предметам, и вы хотите узнать оценку Ким по химии.

Посмотрим, как с помощью XLOOKUP легко решить эту задачу.

• Сначала выполняем «внутреннюю» XLOOKUP для возврата значений всего столбца. XLOOKUP(H2,B1:E1,B2:E10) возвращает диапазон оценок по химии.
• Вкладываем «внутреннюю» XLOOKUP во «внешнюю», используя внутреннюю как return_array в итоговой формуле.
• Вот итоговая формула:

=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))

• Введите формулу выше в пустую ячейку H3 и нажмите Enter, чтобы получить результат.

Или можно сделать наоборот: использовать «внутреннюю» XLOOKUP для возврата значений всей строки, то есть всех оценок Ким по предметам. Затем с помощью «внешней» XLOOKUP найти оценку по химии среди всех оценок Ким.

• Введите приведённую ниже формулу в пустую ячейку H4 и нажмите Enter, чтобы получить результат.

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

Двусторонний поиск в XLOOKUP — отличная иллюстрация его вертикального и горизонтального поиска. Попробуйте сами!

Пример7: Настройка сообщения о ненайденном значении

Настройка сообщения о ненайденном значении с помощью XLOOKUP

Как и другие функции поиска, если совпадение не найдено, возвращается сообщение об ошибке #N/A. Для некоторых пользователей Excel это может быть непонятно. Хорошая новость — обработка ошибок доступна в четвёртом аргументе функции XLOOKUP.

С помощью встроенного аргумента [if_not_found] вы можете указать собственное сообщение вместо результата #N/A. Просто введите нужный текст в необязательный четвёртый аргумент и заключите его в двойные кавычки ("").

Например, город Денвер не найден, поэтому XLOOKUP возвращает ошибку #N/A. Но если мы настроим четвёртый аргумент с текстом «Нет совпадений», формула отобразит этот текст вместо сообщения об ошибке.

Введите приведённую ниже формулу в пустую ячейку F3 и нажмите Enter, чтобы получить результат.

=XLOOKUP(E2,A2:A11,C2:C11,"Нет совпадений")

Настройте ошибку #N/A с помощью удобной функции

Чтобы быстро заменить ошибку #N/A своим сообщением, Kutools для Excel — идеальный инструмент для Excel. Благодаря встроенной функции «Заменить0 или #N/A на пустую ячейку или определённое значение» вы можете указать сообщение о ненайденном значении без сложных формул и доступа к XLOOKUP.

1. Перейдите на вкладку «Kutools» в Excel, найдите «Супер ПОИСК» и выберите «Заменить0 или #N/A на пустую ячейку или определённое значение» в выпадающем списке.

2. В диалоговом окне «Заменить0 или #N/A на пустую ячейку или определённое значение» выполните следующие настройки:

• В разделе «Диапазон значений для поиска и диапазон вывода» выберите диапазон поиска и диапазон вывода;
• Затем выберите опцию «Заменить0 или #N/A на определённое значение», введите нужный текст;
• В разделе «Диапазон данных» выберите диапазон данных, затем укажите «ключевой столбец» и «столбец возврата».

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

Пример8: Несколько значений

Возврат нескольких значений с помощью XLOOKUP

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

В приведённом ниже примере вы хотите получить всю информацию о студенте с ID «FG9940005». Секрет — указать диапазон в return_array формулы, а не отдельный столбец или строку. В данном случае диапазон возврата — B2:D9, то есть три столбца.

Введите приведённую ниже формулу в пустую ячейку G2 и нажмите Enter, чтобы получить все результаты.

=XLOOKUP(F2,A2:A9,B2:D9)

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

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

Пример9. Несколько критериев

Выполнение многокритериального поиска с помощью XLOOKUP

Ещё одна потрясающая новая функция XLOOKUP — возможность поиска по нескольким критериям. Секрет — объединять значения для поиска и массивы поиска с помощью оператора «&» в формуле. Рассмотрим это на примере ниже.

Нам нужно узнать цену средней синей вазы. В этом случае для поиска совпадения требуются три значения (критерия). Введите формулу ниже в пустую ячейку I2 и нажмите Enter, чтобы получить результат.

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

√ Примечание: XLOOKUP может напрямую работать с массивами. Нет необходимости подтверждать формулу сочетанием клавиш Control + Shift + Enter.

Многокритериальный поиск быстрым способом

Есть ли способ выполнить многокритериальный поиск быстрее и проще, чем XLOOKUP в Excel? Kutools для Excel предлагает отличную функцию — «Многокритериальный поиск». С её помощью вы сможете выполнить поиск по нескольким критериям всего за несколько кликов!

1. Перейдите на вкладку «Kutools» в Excel, найдите «Супер ПОИСК» и выберите «Многокритериальный поиск» в выпадающем списке.

2. В диалоговом окне «Многокритериальный поиск» выполните следующие действия:

• В разделе «Диапазон значений для поиска и диапазон вывода» выберите диапазон значений для поиска и диапазон вывода;
• В разделе «Диапазон данных» выполните следующие действия:
  • Выберите соответствующие ключевые столбцы, содержащие значения для поиска, по одному, удерживая клавишу Ctrl в поле «Ключевой столбец»;
  • Укажите столбец, содержащий возвращаемые значения, в поле «Столбец возврата».

3. Нажмите кнопку OK, чтобы получить результат.

√ Примечание:

• Раздел «Заменить не найденные значения (#N/A) указанным значением» в диалоговом окне является необязательным — вы можете его заполнить или оставить пустым.
• Количество столбцов, введённых в поле «Ключевой столбец», должно быть равно количеству столбцов в поле «Диапазон значений для поиска», а порядок критериев в обоих полях должен соответствовать друг другу один к одному.

Пример10. Поиск значения с последним совпадением

Получение последнего совпадающего результата с помощью XLOOKUP

Чтобы найти последнее совпадающее значение в Excel, установите шестой аргумент функции на обратный порядок поиска.

По умолчанию режим поиска в XLOOKUP установлен на1 — поиск от первого к последнему. Но XLOOKUP позволяет изменить направление поиска. Просто установите шестой аргумент [search mode] на -1, и поиск будет выполняться с конца к началу.

Рассмотрим пример ниже. Мы хотим узнать последнюю продажу Эммы в базе данных.

Введите приведённую ниже формулу в пустую ячейку G2 и нажмите Enter, чтобы получить результат.

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

√ Примечание: Четвёртый и пятый аргументы необязательны и в данном случае опущены. Мы указываем только шестой аргумент — -1.

Легко находите последнее совпадающее значение с помощью отличного инструмента

Если у вас нет доступа к XLOOKUP и вы не хотите запоминать сложные формулы, воспользуйтесь функцией «Поиск снизу вверх», чтобы быстро получить результат.

1. Перейдите на вкладку «Kutools» в Excel, найдите «Супер ПОИСК» и выберите «Поиск снизу вверх» в выпадающем списке.

2. В диалоговом окне «Поиск снизу вверх» выполните следующие настройки:

• В разделе «Диапазон значений для поиска и диапазон вывода» выберите диапазон поиска и диапазон вывода;
• В разделе «Диапазон данных» выберите диапазон данных, затем укажите «ключевой столбец» и «столбец возврата».

3. Нажмите кнопку OK, чтобы получить результат.

√ Примечание: Раздел «Заменить не найденные значения (#N/A) указанным значением» в диалоговом окне является необязательным — вы можете его заполнить или оставить пустым.

Скачать пример файла XLOOKUP

XLOOKUP Examples.xlsx

Связанные статьи:

• Как использовать INDEX и MATCH вместе в Excel?
• Как применить распознавание нечетких соответствий для поиска приблизительных совпадений в Excel?
• Как использовать формулу двустороннего поиска в Excel?
• Как выполнить поиск по нескольким критериям в Excel с помощью VLOOKUP?
• Как выполнять поиск значений справа налево в Excel с помощью VLOOKUP?