XLOOKUP в деталях и INDEX/MATCH

В этом уроке ты узнаешь

Использовать аргументы if_not_found, match_mode и search_mode в XLOOKUP
Комбинировать INDEX и MATCH для поиска в любом направлении
Выполнять двумерный поиск по заголовкам строки и столбца
Считать оценки, комиссии и прогрессивный налог по шкалам с приблизительным поиском

В отделе кадров компании есть таблица сотрудников: код, имя, отдел, оклад. Сегодня нужно найти имя по коду, завтра — код по имени (то есть искать влево), потом — продажи на пересечении товара и месяца, а в конце месяца — процент комиссии по объёму продаж. Всё это работа одного семейства — функций поиска. В прошлом уроке ты познакомился с VLOOKUP и XLOOKUP, теперь будем применять их на профессиональном уровне.

Шесть аргументов XLOOKUP

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

где:

lookup_valueискомое значение (код, имя, сумма)
lookup_arrayстолбец или строка, где идёт поиск
return_arrayдиапазон, откуда берётся результат; может состоять из нескольких столбцов
if_not_foundчто вернуть, если ничего не найдено; если не указано — #N/A
match_mode0 — точное (по умолчанию); -1 — точное или ближайшее меньшее; 1 — точное или ближайшее большее; 2 — подстановочные знаки * и ?
search_mode1 — с первого до последнего (по умолчанию); -1 — с последнего к первому; 2 и -2 — двоичный поиск по отсортированным данным

В таблице ниже в диапазоне A2:D7 шесть сотрудников: E101 Aysel Sales 1450, E102 Murad IT 2100, E103 Leyla Finance 1800, E104 Elvin Sales 1350, E105 Nigar IT 2300, E106 Rauf Sales 1500 (оклады в манатах). Обрати внимание, что возвращает каждая формула.

Формула	Результат	Что происходит
`=XLOOKUP("E104",A2:A7,B2:B7)`	Elvin	обычный точный поиск
`=XLOOKUP("Leyla",B2:B7,A2:A7)`	E103	столбец результата левее столбца поиска
`=XLOOKUP("E110",A2:A7,B2:B7,"Нет")`	Нет	свой текст вместо #N/A
`=XLOOKUP("E104",A2:A7,B2:D7)`	Elvin \| Sales \| 1350	возвращается вся строка и «разливается» в соседние ячейки
`=XLOOKUP("Sales",C2:C7,B2:B7)`	Aysel	первое совпадение (сверху)
`=XLOOKUP("Sales",C2:C7,B2:B7,,0,-1)`	Rauf	`search_mode` -1: последнее совпадение
`=XLOOKUP("N*",B2:B7,D2:D7,,2)`	2300	`match_mode` 2: имя на «N» (Nigar)

Пропущенный аргумент (две запятые подряд) сохраняет значение по умолчанию.

INDEX и MATCH: классическая пара

XLOOKUP есть только в Microsoft 365 и начиная с Excel 2021. В старых книгах и в Excel 2019 повсюду встречается пара INDEX/MATCH. Они делят работу: MATCH находит позицию значения в диапазоне, а INDEX возвращает значение, стоящее на указанной позиции (в русской версии — ИНДЕКС и ПОИСКПОЗ).

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

где:

MATCH(…, 0)позиция значения (1, 2, 3…); 0 — точное совпадение
INDEX(range, n)n-й элемент диапазона

Оклад Эльвина через INDEX/MATCH

В таблице сотрудников выше найди оклад сотрудника E104 с помощью INDEX/MATCH. Затем найди код по имени (поиск влево).

Показать решение

1) =MATCH("E104",A2:A7,0) → 4 (E104 — 4-й элемент диапазона).
2) =INDEX(D2:D7,4) → 1350.
3) Вместе: =INDEX(D2:D7,MATCH("E104",A2:A7,0)) → 1350 ₼.
4) Поиск влево: =INDEX(A2:A7,MATCH("Nigar",B2:B7,0)) → MATCH даёт 5, а INDEX возвращает E105.
Диапазоны поиска и результата задаются отдельно, поэтому направление не важно.

Двумерный поиск

Пересечение товара и месяца

В A1:E5 — матрица продаж: в строках товары (Notebook, Pen, Backpack, Calculator), в столбцах месяцы (Jan, Feb, Mar, Apr). Строка Backpack: 40, 55, 38, 60. В H1 записано Backpack, в H2 — Mar. Найди число на пересечении.

Показать решение

INDEX работает с тремя аргументами: диапазон, номер строки, номер столбца.
=INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0))
MATCH(H1,…) → 3 (Backpack — третья строка), MATCH(H2,…) → 3 (Mar — третий столбец).
INDEX(B2:E5,3,3) → 38.
Через XLOOKUP: =XLOOKUP(H1,A2:A5,XLOOKUP(H2,B1:E1,B2:E5)) — внутренний XLOOKUP возвращает весь столбец «Mar», а внешний выбирает из него строку Backpack.

Приблизительный поиск: шкалы и прогрессивный налог

В таблице шкалы записывают нижнюю границу каждой ступени. Нужна наибольшая граница, которая меньше или равна значению. Есть три способа: XLOOKUP(x,limits,results,,-1), VLOOKUP(x,table,2,TRUE) и INDEX(results,MATCH(x,limits,1)). Режим -1 в XLOOKUP работает даже с неотсортированными границами, а VLOOKUP и MATCH требуют границ по возрастанию.

Интерактив

Загрузка симуляции…

100-балльная шкала и буквенные оценки A–F: приблизительный поиск (TRUE).

Комиссия и прогрессивный налог

1) Шкала комиссии: от 0 ₼ — 0%, от 1000 ₼ — 3%, от 5000 ₼ — 5%, от 10000 ₼ — 8%. Продажи за месяц — 7250 ₼. Какова комиссия?
2) Условная учебная шкала прогрессивного налога: до 500 ₼ — 0%, часть от 500 до 2000 ₼ — 10%, часть от 2000 до 5000 ₼ — 20%, часть свыше 5000 ₼ — 30%. Каков налог с дохода 3200 ₼?

Показать решение

1) Границы в E2:E5, ставки в F2:F5: =B1*XLOOKUP(B1,E2:E5,F2:F5,,-1).
Наибольшая граница, не превышающая 7250, — 5000 → 5%.
Комиссия: 7250 · 0,05 = 362,50 ₼.
2) Заранее посчитай в столбце G налог, накопленный до каждой границы: 0, 0, 150 (=1500 · 10%), 750 (=150 + 3000 · 20%).
Налог = накопленный налог + (доход − граница) · ставка.
Для 3200 граница — 2000: 150 + (3200 − 2000) · 0,2 = 150 + 240 = 390 ₼.
Эффективная ставка — 390 / 3200 ≈ 12,19%, а предельная — 20%.

Excel

=XLOOKUP(B1,E2:E5,G2:G5,,-1)+(B1-XLOOKUP(B1,E2:E5,E2:E5,,-1))*XLOOKUP(B1,E2:E5,F2:F5,,-1)

Прогрессивный налог одной формулой: накопленный налог + часть сверх границы × ставка. При B1 = 3200 результат — 390. Реальные ставки зависят от страны и года — для Азербайджана смотри taxes.gov.az.

Переключить тип ссылки: $A$1 → A$1 → $A1 → A1F4

Преобразовать диапазон в таблицу ExcelCtrl+T

Мастер функций (Insert Function) — заполняй аргументы по одномуShift+F3

Вычислить выделенную часть в строке формул (например, увидеть результат MATCH); затем нажми EscF9

Главное

4-й аргумент XLOOKUP заменяет #N/A твоим текстом, 5-й задаёт тип совпадения, 6-й — направление поиска.
INDEX(результат, MATCH(значение, поиск, 0)) работает во всех версиях и ищет влево; не забывай 0 в MATCH.
Двумерный поиск: INDEX с двумя MATCH (строка и столбец) или два вложенных XLOOKUP.
Для шкал записывай нижние границы и используй приблизительный поиск: XLOOKUP -1, VLOOKUP TRUE или MATCH 1 (для двух последних нужен порядок по возрастанию).
Прогрессивный налог = накопленный налог + (доход − граница) × ставка.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Что вернёт =XLOOKUP("Sales",C2:C7,B2:B7,,0,-1) в таблице сотрудников?