Функции MS Excel ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) – более гибкая альтернатива ВПР

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

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

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

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

Работа функций ИНДЕКС и ПОИСКПОЗ

Таким образом, чтобы подтянуть значение цены, соответствующее первому коду (книге), нужно прописать такую формулу.

Комбинация функций ИНДЕКС и ПОИСКПОЗ

Следует обратить внимание на корректность ссылок, чтобы при копировании формулы ничего не «съехало».

Протягивая формулу вниз, заполняем весь столбец. Если в таблице, откуда подтягиваются данные, нет искомого критерия, то функция выдает ошибку #Н/Д, что и показано на рисунке ниже.

Ошибки при использовании функций ИНДЕКС и ПОИСКПОЗ

Довольно стандартная ситуация, с которой успешно справляется функция ЕСЛИОШИБКА. Она перехватывает ошибки и вместо них выдает что-либо другое, например, нули.

Конструкция формулы будет следующая:

ЕСЛИОШИБКА, ИНДЕКС и ПОИСКПОЗ

Вот, собственно, и все.

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

Ниже видеоурок по работе функций ИНДЕКС и ПОИСКПОЗ.

Скачать файл с примером.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •