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

Итак, в нашем распоряжении - таблица с товарами и различными видами цен на них. В зависимости от того, какой из показателей нам нужен, необходимо изменить номер столбца с данными.

Данные, которые нас интересуют, находятся в столбцах G, H, I. В диапазоне A1:I7 они имеют номера 4, 5, 6. Как нам получить эти порядковые номера и как изменять их, не корректируя формулу ВПР?

Для этого нам пригодится функция ПОИСКПОЗ (MATCH). Она как раз то и возвращает номер определенного значения в диапазоне данных. Мы должны указать это значение в ячейке В1.

Для того, чтобы избежать ошибок при вводе, создадим выпадающий список в B1.

Как сделать выпадающий список в Excel? Читайте подробную инструкцию.

Получаем следующую картину:

Теперь мы гарантированно выберем нужный показатель: ошибки при вводе исключены.

Определить номер нужного столбца при помощи ПОИСКПОЗ можно так:

=ПОИСКПОЗ($B$1;$D$1:$I$1;0)

Поясним: мы ищем значение из В1 в диапазоне D1:I1 с точным совпадением.

Подставим эту формулу в функцию ВПР вместо параметра "номер столбца". Получим:

=ВПР(A2;$D$2:$I$7;ПОИСКПОЗ($B$1;$D$1:$I$1;0);0)

Вот как это выглядит:

Скопируйте нашу формулу для всех товаров.

Теперь ВПР ищет нужное наименование товара в D и затем извлекает для него ту цену, которая выбрана в выпадающем списке в ячейке В1. Задача динамического изменения номера столбца с извлекаемыми данными решена.

Полезная информация о функции ВПР:

ВПР с несколькими таблицами 2 способа извлечь данные из разных таблиц при помощи ВПР. - Задача: Данные, которые нужно найти и извлечь при помощи функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть, одни и те же столбцы, расположенные в одном…
4 способа, как сделать левый ВПР в Excel. - Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…
Формула ВПР в Excel для сравнения двух таблиц - 4 способа - Сравнение таблиц – это задача, которую в Excel приходится довольно часто решать. Например, у нас есть старый прайс-лист и его новая версия. Нужно просмотреть, цены на какие товары изменились и…
Почему не работает ВПР в Excel? - Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…
ВПР с несколькими условиями: 5 примеров. - Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…
Функция ВПР в Excel: пошаговая инструкция с 5 примерами - ВПР - это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска…
Формула ВПР в Excel — 22 факта, которые нужно знать. - В процессе работы в Excel часто возникает задача извлечения нужных данных из рабочих таблиц. Для этой цели в Excel предусмотрена формула ВПР (VLOOKUP в английском варианте). И хотя ВПР относительно…