ВПР vs. ИНДЕКС & ПОИСКПОЗ⁠⁠

ВПР – классическая, даже, пожалуй, легендарная функция. Эдакий первый этап для многих на пути к становлению активным пользователем функций в Excel. Тем не менее, у ВПР есть целый ряд недостатков: во-первых, ВПР не может возвращать значения, находящиеся слева от первого указанного столбца. Во-вторых, достаточно проблематичным может оказаться перемещение столбцов в таблице, на которую уже ссылаются определенные функции ВПР.

Именно поэтому, в этом видео я хочу тебе рассказать про использование комбинации функций ИНДЕКС и ПОИСКПОЗ в качестве универсальной и более мощной замены функции ВПР. Рассматривать будем пример из предыдущего поста с прошлой недели.

Итак, при выборе значения в динамическом выпадающем списке ячейки С2, в ячейках С3:С5 должны подтягиваться значения из умной таблицы ниже (как добавляются и работают динамические выпадающие списки в Excel мы рассмотрели вот в этом посте):

Сперва пропишем формулу в ячейке С3, а в оставшихся двух затем просто вставим копию готовой формулы.

Итак, ставим знак равно и прописываем функцию ИНДЕКС:

Данная функция работает следующим образом. В ней мы выбираем определенный диапазон (в нашем случае это будет диапазон значения умной таблицы – использовать будем структурированную ссылку), и посредством указания номера строки и номера столбца мы получаем определенную ячейку нашей таблицы.

Вот визуализированный пример, в котором таким образом мы выделяем ячейку со значение «Ford Mondeo» в области значений умной таблицы, выделяя четвертую строку и второй столбец:

Так что указываем первый аргумент в нашей функции ИНДЕКС:

Ну и следующие два аргумента функции индекс служат для указания номера строки и столбца. Естественно, указывать эти номера мы будем динамическим способом, а именно при помощи функции ПОИСКПОЗ.

Вот что делает эта функция: ей мы указываем определенный диапазон (второй аргумент), и говорим, какое значение нужно искать в этом диапазоне (первый аргумент):

Найдя значение в указанном диапазоне, ПОИСКПОЗ возвращает его порядковую позицию – в случае строки эту будет индекс 7. И да, в последнем аргументе функции ПОИСКПОЗ выставляем 0 для поиска точного совпадения.

Ну и тоже самое делаем для третьего аргумента функции ИНДЕКС – с помощью функции ПОИСКПОЗ динамическим образом указываем нужный нам столбец:

Супер! Теперь просто протягиваем формулу вниз и всё готово:

Ну и теперь о плюсах этой формулы в сравнении с ВПР: во-первых, столбец искомых значений в отличии от ВПР может находиться на любой позиции, и поиск может происходить как, скажем так, влево, так и вправо от диапазона с искомыми значениями. Во-вторых, даже если на нашу таблицу ссылаются уже прописанные комбинации ИНДЕКС и ПОИСКПОЗ, мы всегда абсолютно спокойно можем изменять позиции отдельных столбцов. Вот пример с теми же формулами без единого изменения:

Вот такая интересная, и очень практичная формула!

Ну и напоследок приглашаю тебя на свой YouTube-канал, посмотреть вот это видео:

В нём я еще более углубленно и с упоминанием других важных пунктов рассказал о ИНДЕКС и ПОИСКПОЗ, а также привел пример практического применения комбинации этих функций для создания автоматических формуляров.

Приятного просмотра! 😊