Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом столбце, а извлекать значения можно только их тех, которые находятся правее. А как же быть, если наш ключевой столбец находится в середине таблицы, а данные нужно извлечь из левой ее части? Как сделать «левый» ВПР в таблице Excel?

Мы предложим вам несколько способов извлечения значений, находящихся слева от диапазона поиска. А вот какой из них будет для вас понятнее и удобнее – решайте сами.

Итак, у нас есть данные о продажах. Необходимо по коду заказа вывести наименование товара и его стоимость. При этом колонка с кодом находится в середине таблицы, а наименования – левее него. Такой случай как раз и принято называть «левый (или обратный) ВПР».

Получить стоимость по нужному заказу не представляет труда, и с такой задачей ВПР прекрасно справляется. А вот для того, чтобы вывести в С6 название товара, стандартных возможностей этой функции будет недостаточно. Ведь она "работает только направо".

1. Дополнительный столбец.

Создадим дополнительный столбец с названиями товаров справа от ключевого. Для этого в ячейке H10 запишем формулу

=B10

Скопируем ее вниз и получим новый список, расположенный справа от диапазона поиска.Точнее говоря, это точная копия.

Формула в С6 тогда будет такова:

=ВПР(C2;C9:H25;6;0)

6 - это порядковый номер новой колонки с названиями напитков, который мы как бы переместили (продублировали) слева на правую сторону. Отсчет мы начинаем с С.

Если это слишком портит вид вашей таблицы и попадает в распечатку, можете ее скрыть. Для этого щелкните на букве H правой кнопкой мыши и из выпадающего меню выберите «Скрыть». На расчеты это никак не повлияет.

2. Виртуальная перестановка данных.

В отличие от предыдущего способа, где мы реально меняли расположение данных, здесь мы сделаем это виртуально.

Для этого нам понадобится функция ВЫБОР (CHOOSE в английском варианте). Она позволяет выбрать данные из какого-то массива по их индексу. Проще говоря, по порядковому номеру колонки.

Для поиска напитка используем формулу:

=ВПР(C2;ВЫБОР({1;2};C10:C25;B10:B25);2;0)

Вся хитрость состоит в указании массива данных для поиска. Мы выбираем два диапазона: С10:С25 – с кодами (первый), и В10:В25 – с напитками (второй). И из них при помощи ВЫБОР создаем новую виртуальную таблицу, с которой и будем работать.

Конструкция в виде массива {1;2} показывает, что мы берем сначала первый указанный диапазон C10:C25, потом – второй B10:B25, и формируем из них виртуальную таблицу с двумя колонками. В ней мы и производим поиск по первой и извлекаем значения – из второй. И при этом не важно, где они реально расположены на листе Excel. ВПР работает с виртуальным массивом, который мы сами создали.

3. Левый ВПР при помощи ИНДЕКС + ПОИСКПОЗ.

И снова – об альтернативах функции ВПР, которые часто оказываются ничем не хуже, а даже и лучше.

Напомним, что функция ИНДЕКС (INDEX) позволяет извлечь элемент из перечня по его порядковому номеру. И этот номер нам поможет определить ПОИСКПОЗ (или MATCH).

Формула «обратного ВПР» выглядит теперь так:

=ИНДЕКС(B10:B25;ПОИСКПОЗ(C2;C10:C25;0))

Давайте подробно разберем последовательность вычислений. В начале используем ПОИСКПОЗ(что_ищем; где_ищем; тип_поиска). Тип поиска – 0, то есть точное совпадение. В диапазоне C10:C25 мы ищем нужное нам значение, указанное в С2, и получаем его порядковый номер. В нашем случае это 2.

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

4. Извлекаем из столбца слева числовые значения.

Этот «обратный ВПР» подходит только для чисел. Предположим, нам нужно подсчитать количество литров в определенном заказе. Но объем также находится левее, чем расположен его код.

Используем ВПР в комбинации с СУММПРОИЗВ (SUMPRODUCT).

Наша итоговая формула:

=СУММПРОИЗВ((ВПР(C2;D10:D25;1;0)=D10:D25)*C10:C25)

Давайте пошагово разберем все действия.

Сразу вспомним важную особенность СУММПРОИЗВ: она работает с предложенным ей диапазоном как с массивом, то есть последовательно перебирает все его элементы от начала до конца.

Вначале ВПР(C2;D10:D25;1;0) позволяет нам найти в перечне заказов интересующий нас и вывести его же (порядковый номер колонки 1, то есть, что ищем, то и возвращаем).  Если будет получена ошибка, то она автоматически в нашем случае функцией СУММПРОИЗВ преобразуется в ноль. Сравниваем извлеченное с соответствующим значением из перечня кодов при помощи равенства:  ВПР(C2;D10:D25;1;0)=D10:D25

В случае соответствия получаем ИСТИНА (что соответствует 1), в противном случае – ЛОЖЬ (или 0).

В итоге получаем виртуальный набор цифр, состоящий из 0 и 1. Поскольку коды у нас не повторяются, единица будет в этом списке всего одна. Перемножаем этот набор нулей и единиц с соответствующими величинами объема C10:C25. Все произведения дадут нам 0, кроме одного – соответствующего нашему, где была 1.  Общая сумма этих произведений и даст нам искомый объем по нужной позиции.

И еще один вариант левого ВПР с числовыми значениями. На этот раз используем функцию СУММЕСЛИ (SUMIF).

Она позволяет находить сумму в зависимости от выполнения определенных условий. Смотрите пример.

Формула расчета:

=СУММЕСЛИ(D10:D25;C2;C10:C25)

Синтаксис здесь такой:

СУММЕСЛИ(диапазон_просмотра; критерий; что_суммируем).

Находим сумму чисел из диапазона C10:C25, соответствующих тем ячейкам из D10:D25, которые равны С2.

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

Дополнительные материалы о функции ВПР:

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