Примеры формул ИНДЕКС и ПОИСКПОЗ для выборки из списка в Excel

Часто случается так что при считывании значений столбцов таблиц поиск значений для выборки следует выполнять по крайним правым столбцам. Excel предлагает несколько функций возвращающих значения ячеек находящиеся по левой стороне столбца, по которому и выполняется поиск.

Как применить формулу ИНДЕКС и ПОИСКПОЗ в Excel

Ниже на рисунке представлены города и области, в которых находятся магазины сети. Допустим, что после выбора пользователем названия области с выпадающего списка в ячейке G1 должны автоматически заполнится поля «Город» G2 и «№ магазина» G3:

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

Формула для поиска номера магазина по названию города возвращает значения из четвертого столбца:

Поэтому последний аргумент в функции ВПР равен числу 4.

Если диапазон ячеек не начинается с ячейки A1 аргументы, определяющие строку и столбец, не соответствуют номерам строк и столбцов рабочего листа Excel. Отсчет номеров производится, начиная от ячейки левого верхнего угла указанного диапазона (в данном примере это A2) в первом аргументе функции ИНДЕКС. Например, формула =ИНДЕКС(G2:P10;2;2) возвращает значение ячейки H3, которая находится во второй строке второго столбца диапазона G2:P10.

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

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

1. Искомое значение – то что следует найти.
2. Просматриваемый массив – одномерный массив или диапазон ячеек из одной строки или из одного столбца где должно быть найдено искомое значение.
3. Тип сопоставления – точность соответствия (необязательный аргумент для заполнения), при точном соответствии указывается значение ЛОЖЬ или 0. Если требуется неточное совпадение значений, тогда ИСТИНА или 1.

В данном примере искомым значением выступает название области указана в ячейке G1. Данное значение формула ищет в списке областей диапазона C2:C11. Функция ПОИСКПОЗ поочередно проверяет все ячейки, пока не найдет строку «Свердловская», находящееся на 5-ой позиции. После чего данная функция возвращает значение 5, которое будет использовано как второй аргумент с номером строки для функции ИНДЕКС.

После возвращения результата через функцию ПОИСКПОЗ функция ИНДЕКС будет иметь все необходимые данные для отображения соответственного значения. Перейдет на 5-тую строку диапазона ячеек и выберет значение из первого столбца «Город» или из четвертого «№ магазина».

Внимание! Если в аргументах функции ИНДЕКС указать номер строки больше чем количество строк в диапазоне ячеек или номер столбца больше чем количество столбцов, тогда функция возвращает ошибку #ССЫЛКА!



Как сделать выборку из списка формулой ПРОСМОТР в Excel

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

1. Искомое значение – значение позицию которого необходимо найти.
2. Просматриваемый вектор – одномерный массив или диапазон ячеек из одной строки или из одного столбца где будет вестись поиск искомого значения.
3. Вектор результатов - одномерный массив или диапазон ячеек из одной строки или из одного столбца из которого следует вернуть результат (необязательный для заполнения).

Внимание! Сразу же отметим недостатки функции ПРОСМОТР, а они весьма существенные:

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

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

И соответственного номера магазина:

Таблица пересортирована по диапазону C2:C11 по возрастанию и теперь все работает как надо.

Первые два аргумента функции ПРОСМОТР такие же, как и у функции ПОИСКПОЗ. Эти две функции работают по одному и тому же принципу. То есть ищет заданное значение в одном направлении вектора. Отличие в том, что она возвращает не позицию где находится найденная ячейка в диапазоне с нужным значением, а содержимое соответственной ячейки находящиеся на позиции (указанной в векторе результатов) от нее. Ну и не стоит забывать, что для функции ПРОСМОТР нужно сортировать таблицу по возрастанию просматриваемого диапазона и у нее нет аргумента точности совпадения при поиске значений.

Чтобы найти необходимый город, функция ПРОСМОТР сначала определяет, что строка «Свердловская» находится на 10-ой позиции (после сортировки таблицы) просматриваемого вектора по диапазону C2:C11. После формула чего возвращает содержимое 10-ой ячейки, но уже по вектору в диапазоне A2:A11. Аналогичным принципом функция ищет номер магазина, но там результирующий вектор – это уже диапазон D2:D11.