Общая формула
=XLOOKUP(value,rng1,rng2,,-1)
Резюме
Чтобы использовать XLOOKUP для поиска приблизительного совпадения, явно укажите match_mode в четвертом аргументе. В показанном примере формула в F5, скопированная в таблицу, выглядит так:
=XLOOKUP(E5,qty,disc,,-1)
где количество (B5: B13) и диск (C5: C13) являются именованными диапазонами.
Объяснение
В показанном примере таблица в B4: C13 содержит скидки на основе количества. По мере увеличения количества увеличивается и скидка. Таблица в E4: F10 показывает скидку, возвращаемую XLOOKUP для нескольких случайных количеств. XLOOKUP настроен на использование количества в столбце E для поиска соответствующей скидки. Скопированная формула в F5:
=XLOOKUP(E5,qty,disc,-1)
- Lookup_value поступает из ячейки E5
- Поисковый_массив является именованный диапазон кол - во (B5: B13)
- Return_array - это диск с именованным диапазоном (C5: C13)
- Аргумент not_found не указан
- Match_mode установлен на -1 (точное совпадение или следующее меньшее)
- Search_mode не предоставляется и по умолчанию равен 1 (от первого до последнего)
Примечание: имейте в виду, если вы указываете пустую строку ("") для not_found. Если совпадений не найдено, XLOOKUP ничего не отобразит вместо # N / A. Другими словами, пустая строка не означает NULL, это означает «ничего не отображать, если совпадение не найдено».
В каждой строке XLOOKUP ищет количество в столбце E в диапазоне B5: B13. При обнаружении точного соответствия возвращается соответствующая скидка в столбце C. Если точное совпадение не найдено, возвращается скидка, связанная со следующим наименьшим количеством.
XLOOKUP против VLOOKUP
Эквивалентная формула ВПР для этого примера - одна из двух формул ниже:
=VLOOKUP(E5,B5:C13,2) // default approximate =VLOOKUP(E5,B5:C13,2,1) // explicit approximate
Есть несколько заметных отличий:
- Для ВПР в качестве второго аргумента требуется полный массив таблиц. XLOOKUP требует только диапазон со значениями поиска.
- ВПР требует, чтобы индекс столбца определял столбец результата. XLOOKUP требует диапазона, содержащего значения результатов.
- По умолчанию ВПР дает приблизительное совпадение. XLOOKUP по умолчанию выполняет точное соответствие.
- Функция VLOOKUP требует сортировки данных поиска по значению поиска. XLOOKUP работает с несортированными данными.
