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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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