XLOOKUP функция

Използвайте функцията XLOOKUP , за да намирате неща в таблица или диапазон по ред. Например търсите цената на дадена автомобилна част по номера на част или намерете името на служител въз основа на неговия ИД на служител. С XLOOKUP можете да търсите в една колона израз за търсене и да върнете резултат от същия ред в друга колона, независимо от коя страна е колоната за връщане.

Синтаксис

Функцията XLOOKUP търси в диапазон или масив, след което връща елемента, съответстващ на първото съвпадение, което намери. Ако не съществува съвпадение, XLOOKUP може да върне най-близкото (приблизително) съвпадение. 

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

Аргумент	Описание
справка_стойност Задължително*	Стойността, която ще бъде търсена *Ако липсва, 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 – Изшършва двоично търсене, което разчита на сортирането lookup_array във възходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати. -2 – Изшършва двоично търсене, което разчита на „търсен_масив“, който се сортира във низходящ ред. Ако не се сортира, ще бъдат върнати невалидни резултати.

Примери

Пример 1 използва XLOOKUP, за да търси име на страна в диапазон и след това да върне телефонния й код на страна. Тя включва аргументите lookup_value (клетка F2), lookup_array (диапазон B2:B11) и return_array (диапазон D2:D11). Той не включва аргумента match_mode , тъй като XLOOKUP показва точно съвпадение по подразбиране.

———————————————————————————

Пример 2 търси информация за служител въз основа на идентификационен номер на служител. За разлика от VLOOKUP, XLOOKUP може да върне масив с множество елементи, така че една формула може да върне както име на служител, така и отдел от клетки C5:D14.

———————————————————————————

Пример 3 добавя if_not_found аргумент към предишния пример.

———————————————————————————

Пример 4 търси в колона C личния доход, въведен в клетка E2, и намира съответстваща данъчна ставка в колона B. Тя задава аргумента if_not_found да връща 0 (нула), ако нищо не е намерено. Аргументът match_mode е зададен на 1, което означава, че функцията ще търси точно съвпадение и ако не може да намери такова, ще върне следващия по-голям елемент. И накрая, аргументът search_mode е зададен на 1, което означава, че функцията ще търси от първия до последния елемент.

———————————————————————————

Пример 5 използва вложена функция XLOOKUP, за да извърши и вертикално, и хоризонтално съвпадение. Тя първо търси брутната печалба в колона B, след това търси Qtr1 в горния ред на таблицата (диапазонът C5:F5) и накрая връща стойността в пресечната точка на двете. Това е подобно на използването на функциите INDEX и MATCH заедно.

———————————————————————————

Пример 6 използва функцията SUM и две вложени функции XLOOKUP, за да сумира всички стойности между два диапазона. В този случай искаме да сумираме стойностите за грозде, банани и да включим крушите, които са между двете.

Формулата в клетка E3 е: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Как работи? XLOOKUP връща диапазон, така че когато се изчислява, формулата завършва така: =SUM($E$7:$E$9). Можете да видите как работи това сами, като изберете клетка с формула XLOOKUP, подобна на тази, след което изберете "Проверка> на формули >за формули" Изчисляване на формула и след това изберете "Изчисляване", за да преминете през изчислението. 

———————————————————————————