Сообщить об ошибкеКороткий ответ
VLOOKUP ищет значение только в первом столбце диапазона и возвращает значение из столбца правее по фиксированному номеру, а INDEX+MATCH ищет в любом столбце и возвращает значение из любого другого столбца независимо от порядка, что делает его устойчивее к добавлению новых колонок.
Как это работает подробнее
- Как это работает: VLOOKUP(искомое; диапазон; номер_столбца; ЛОЖЬ) берёт N-й столбец от начала диапазона; MATCH находит позицию значения в одном диапазоне, а INDEX возвращает значение из другого диапазона по этой позиции, то есть столбец поиска и столбец возврата не обязаны быть рядом.
Пример кода
lookup_formula.txt
=ИНДЕКС(Справочник!$C$2:$C$1000; ПОИСКПОЗ(A2; Справочник!$A$2:$A$1000; 0))Что использовать на практике
- На практике: если в таблицу позже добавляют новый столбец между столбцом поиска и столбцом возврата, формула VLOOKUP с жёстко заданным номером столбца начнёт возвращать неверные данные, а INDEX+MATCH продолжит работать правильно, так как ссылается на диапазоны, а не на номер по счёту.
- Пример и проверка. Таблица заказов в столбце A — ID заказа, в столбце C — сумма. Формула =ВПР(A2; Справочник!A:C; 3; ЛОЖЬ) сломается, если в «Справочник» вставят новый столбец B. Устойчивый вариант: Проверка: добавить тестовый столбец в справочник и убедиться, что INDEX+MATCH продолжает возвращать то же значение, а VLOOKUP с фиксированным номером — нет.
Подводные камни
- Частые ошибки: забыть указать последний аргумент VLOOKUP как ЛОЖЬ/FALSE (точное совпадение), из-за чего формула находит ближайшее меньшее значение в отсортированном диапазоне; использовать относительные ссылки на диапазон поиска, из-за чего при копировании формулы вниз диапазон «съезжает» и часть строк выдаёт ошибку #Н/Д.