Общая формула
=XLOOKUP(value,rng1,rng2,,-1) // approximate match =XLOOKUP(value,rng1,rng2) // exact match
Резюме
Чтобы выполнить горизонтальный поиск с помощью функции XLOOKUP, предоставьте значение поиска, массив поиска и массив результатов. При необходимости укажите аргумент match_mode для приблизительного совпадения. В показанном примере формула в I5, скопированная в таблицу, выглядит так:
=XLOOKUP(E5,quantity,discount,,-1)
где количество (C4: F4) и скидка (C5: F5) являются именованными диапазонами.
Объяснение
Одним из приятных преимуществ функции XLOOKUP является то, что синтаксис, используемый для горизонтального поиска, такой же, как и для вертикального поиска.
В показанном примере данные в C4: F5 содержат скидки на основе количества. По мере увеличения количества увеличивается и скидка. В таблице справа показана скидка, возвращаемая XLOOKUP для нескольких случайных количеств. Скопированная формула в F5:
=XLOOKUP(E5,quantity,discount,,-1)
Та же формула без именованных диапазонов:
=XLOOKUP(H5,$C$4:$F$4,$C$5:$F$5,,-1)
Аргументы XLOOKUP конфигурируются следующим образом:
- Lookup_value поступает из ячейки E5
- Lookup_array - это количество именованного диапазона (C4: F4)
- Return_array - это скидка на именованный диапазон (C5: F5)
- Аргумент not_found не указан
- Match_mode установлен на -1 (точное совпадение или следующее меньшее)
- Search_mode не предоставляется и по умолчанию равен 1 (от первого до последнего)
В каждой строке XLOOKUP ищет количество в диапазоне C4: F4. При обнаружении точного соответствия возвращается соответствующая скидка в строке 5. Если точное совпадение не найдено, возвращается скидка, связанная со следующим наименьшим количеством.
XLOOKUP против HLOOKUP
Эквивалентная формула HLOOKUP для этого примера - одна из двух формул ниже:
=HLOOKUP(H5,$C$4:$F$5,2) // default approximate =HLOOKUP(H5,$C$4:$F$5,2,1) // explicit approximate
Стоит отметить несколько отличий:
- HLOOKUP требует в качестве второго аргумента полный массив таблиц. XLOOKUP требует только диапазон со значениями поиска.
- HLOOKUP требует индекса строки для определения столбца результата. XLOOKUP требует диапазона, содержащего значения результатов.
- HLOOKUP по умолчанию дает приблизительное совпадение. XLOOKUP по умолчанию выполняет точное соответствие.
- HLOOKUP требует сортировки данных поиска по значению поиска. XLOOKUP работает с несортированными данными.
- HLOOKUP выполняет только горизонтальный поиск, XLOOKUP может выполнять как горизонтальный, так и вертикальный поиск.
