Находите данные по строкам в таблице или диапазоне с помощью функции ПРОСМОТРX. Например, можно найти цену автомобильной детали по ее номеру или найти имя сотрудника по его идентификатору. С помощью функции ПРОСМОТРX вы можете искать в одном столбце искомый запрос и возвращать результат из той же строки в другом столбце, независимо от того, на какой стороне находится возвращаемый столбец.
Примечание
XLOOKUP недоступен в Excel 2016 и Excel 2019. Однако вы можете столкнуться с ситуацией использования книги в Excel 2016 или Excel 2019 с функцией XLOOKUP в ней, если она была создана другим пользователем с использованием более новой версии Excel.
Синтаксис
Функция ПРОСМОТРX выполняет поиск в диапазоне или массиве, а затем возвращает элемент, соответствующий первому найденному совпадению. Если совпадение отсутствует, функция ПРОСМОТРX может вернуть ближайшее (приблизительное) совпадение.
=ПРОСМОТРХ(искомое_значение; просматриваемый_массив; возращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])
| Аргумент | Описание |
|---|---|
|
Искомое_значение. Обязательно* |
Значение для поиска *Если этот параметр опущен, функция XLOOKUP возвращает пустые ячейки, которые он находит в lookup_array. |
|
Просматриваемый_массив Обязательно |
Массив или диапазон для поиска |
|
return_array Обязательный |
Возвращаемый массив или диапазон |
|
[if_not_found] Необязательный |
Если допустимое совпадение не найдено, верните текст [if_not_found], который вы указали. Если допустимое совпадение не найдено и [if_not_found] отсутствует, возвращается #N/A. |
|
[режим_сопоставления] Необязательно |
Укажите тип сопоставления: 0 — точное совпадение. Если ничего не найдено, верните #N/A. Этот параметр используется по умолчанию. -1 — точное совпадение. Если ни один элемент не найден, верните следующий элемент меньшего размера. 1 — точное совпадение. Если ни один элемент не найден, верните следующий более крупный элемент. 2 — совпадение с использованием особого значения подстановочных знаков: *, ?, ~. |
|
[режим_поиска] Необязательно |
Укажите используемый режим поиска: 1 — выполнение поиска, начиная с первого элемента. Этот параметр используется по умолчанию. -1 — выполнение обратного поиска, начиная с последнего элемента. 2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по возрастанию. Если сортировка не выполнена, будут возвращены недопустимые результаты. -2 — выполнение двоичного поиска на основе сортировки просматриваемого_массива по убыванию. Если сортировка не выполнена, будут возвращены недопустимые результаты. |
Примеры
В примере 1 используется XLOOKUP для поиска названия страны в диапазоне, а затем возврата ее телефонного кода страны. Он включает аргументы lookup_value (ячейка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Он не включает аргумент match_mode, так как по умолчанию функция ПРОСМОТРХ создает точное совпадение.
Примечание
Функция ПРОСМОТРX использует массив поиска и возвращаемый массив, тогда как функция ВПР использует один массив таблиц, за которым следует индексный номер столбца. Эквивалентная формула ВПР в этом случае будет: =ВПР(F2;B2:D11;3;FALSE)
———————————————————————————
В примере 2 выполняется поиск сведений о сотрудниках на основе идентификатора сотрудника. В отличие от ВПР, функция ПРОСМОТРX может возвращать массив из нескольких элементов, поэтому одна формула может возвращать как имя сотрудника, так и отдел из ячеек C5:D14.
———————————————————————————
В примере 3 к предыдущему примеру добавляется аргумент if_not_found .
———————————————————————————
В примере 4 в столбце C выполняется поиск личного дохода, указанного в ячейке E2, и поиск соответствующей налоговой ставки в столбце B. Он задает аргумент if_not_found для возврата 0 (ноль), если ничего не найдено. Аргумент match_mode имеет значение 1, что означает, что функция будет искать точное совпадение, и если не удается найти его, она возвращает следующий более крупный элемент. Наконец, аргумент search_mode имеет значение 1, что означает, что функция будет выполнять поиск от первого элемента к последнему.
Примечание
Столбец XARRAY lookup_array находится справа от столбца return_array, тогда как ВПР может выполнять поиск только слева направо.
———————————————————————————
В примере 5 используется вложенная функция XLOOKUP для выполнения вертикального и горизонтального совпадения. Сначала выполняется поиск валовой прибыли в столбце B, затем выполняется поиск Qtr1 в верхней строке таблицы (диапазон C5:F5) и, наконец, возвращается значение на пересечении двух этих значений. Это аналогично совместному использованию функций ИНДЕКС и ПОИСКПОЗ.
Совет
Для замены функции ГПР можно также использовать функцию ПРОСМОТРX.
Примечание
Формула в ячейках D3:F3: =ПРОСМОТРX(D2,$B6:$B17,ПРОСМОТРX($C3,$C5:$G5,$C6:$G17)).
———————————————————————————
В примере 6 используется функция SUM и две вложенные функции XLOOKUP для суммирования всех значений между двумя диапазонами. В этом случае мы хотим суммировать значения для винограда, бананов и включить груши, которые находятся между ними.
Формула в ячейке E3: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Как это работает? XLOOKUP возвращает диапазон, поэтому при вычислении формула будет выглядеть следующим образом: =SUM($E$7:$E$9). Вы можете увидеть, как это работает самостоятельно, выбрав ячейку с формулой XLOOKUP, аналогичной этой, а затем выберите ФормулыАудит> формулы >Вычислить формулу, а затем выберите Оценить, чтобы выполнить вычисление.
Примечание
Благодаря Microsoft Excel MVP , Билл Елен, за то, что он предложил этот пример.
———————————————————————————