Использование подстановочных знаков в VLOOKUP и XLOOKUP в Excel

Использование подстановочных знаков в ВПР / VLOOKUP и ПРОСМОТРХ (XLOOKUP) в Excel

Функции ВПР / VLOOKUP и ПРОСМОТРХ (XLOOKUP) — мощные инструменты для поиска данных в Excel. В этой статье мы рассмотрим, как использовать подстановочные знаки для поиска неточных совпадений и как организовать поиск по нескольким таблицам, что особенно полезно при работе с нестандартизированными данными.

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

Поиск с подстановочными знаками

Подстановочные знаки, такие как звёздочка (*), позволяют находить значения, даже если вы не знаете точную форму записи. Например, если в таблице могут встречаться варианты "Apple", "Apple Computer" или "Apple Computer Inc", можно использовать шаблон "Apple*".

Синтаксис для ВПР / VLOOKUP

=ВПР("Apple*"; Table; 2; ЛОЖЬ)

=VLOOKUP("Apple*", Table, 2, FALSE)

Эта формула найдёт первую запись в диапазоне поиска, которая начинается с "Apple".

Синтаксис для ПРОСМОТРХ (XLOOKUP)

В ПРОСМОТРХ (XLOOKUP) необходимо явно указать режим поиска с подстановочными знаками, используя аргумент match_mode.

=ПРОСМОТРХ("Apple*"; A; B; ; 2)

=XLOOKUP("Apple*", A, B, , 2)

Здесь 2 в пятом аргументе указывает на поиск с подстановочными знаками.

Поиск в нескольких таблицах

Часто данные могут быть распределены по нескольким таблицам. Например, нужно сначала искать в Table1, а если совпадение не найдено — в Table2.

Комбинация ВПР / VLOOKUP и ЕСНД / IFNA

Функция ЕСНД / IFNA позволяет задать альтернативное действие при ошибке #Ч / N/A.

=ЕСНД(ВПР(A2; Table1; 3; ЛОЖЬ); ВПР(A2; Table2; 3; ЛОЖЬ))

=IFNA(VLOOKUP(A2, Table1, 3, FALSE), VLOOKUP(A2, Table2, 3, FALSE))

Если первый ВПР / VLOOKUP возвращает ошибку, выполняется второй.

Решение с ПРОСМОТРХ (XLOOKUP)

В ПРОСМОТРХ (XLOOKUP) аргумент для обработки ошибки #Ч / N/A встроен прямо в функцию.

=ПРОСМОТРХ(A2; A; B; ПРОСМОТРХ(A2; C; D))

=XLOOKUP(A2, A, B, XLOOKUP(A2, C, D))

Если первый поиск не даёт результата, выполняется вложенный ПРОСМОТРХ (XLOOKUP).

Практические советы

1. Используйте подстановочные знаки для работы с данными, которые могут иметь вариации в написании (например, названия компаний или товаров).
2. Комбинируйте ЕСНД / IFNA с функциями поиска для создания отказоустойчивых формул.
3. Помните, что ПРОСМОТРХ (XLOOKUP) более гибок и имеет встроенную обработку ошибок, что упрощает формулы.

Освоение этих приёмов значительно повысит эффективность вашей работы с данными в Excel, особенно при анализе больших и неструктурированных наборов информации.