ИНДЕКС и ПОИСКПОЗ в Excel
Связка, которая ищет в любую сторону и не ломается при вставке столбца. Шесть формул: поиск влево, двумерный поиск и защита от #Н/Д.
Коротко
Функции делят работу. ПОИСКПОЗ отвечает где: возвращает номер строки, в которой нашлось значение. ИНДЕКС отвечает что: берёт из диапазона значение по этому номеру.
Вместе получается =ИНДЕКС(что_вернуть;ПОИСКПОЗ(что_искать;где_искать;0)). Ноль в конце — точное совпадение, и его ставят почти всегда: без него ПОИСКПОЗ ищет приблизительно и требует сортировки.
Главное отличие от ВПР: что_вернуть и где_искать — два независимых диапазона. Поэтому связка ищет влево, не привязана к порядку столбцов и не сдвигается, когда в середину справочника вставляют новый столбец. ВПР в обоих случаях ломается.
То же самое формулой в Excel
=ИНДЕКС($B$6:$B$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0)) — поиск влево: артикул по названию — ВПР так не умеет=ИНДЕКС($D$6:$D$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0)) — цена по названию=ПОИСКПОЗ($H$4;$C$6:$C$10;0) — ПОИСКПОЗ отдельно: какая это строка по счёту в диапазоне=ИНДЕКС($B$6:$E$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0);3) — двумерный ИНДЕКС: строка по поиску, столбец по номеру=ИНДЕКС($B$6:$E$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0);ПОИСКПОЗ("Остаток";$B$5:$E$5;0)) — два ПОИСКПОЗ: и строка, и столбец ищутся по заголовку=ЕСЛИОШИБКА(ИНДЕКС($D$6:$D$10;ПОИСКПОЗ($H$4;$C$6:$C$10;0));"не найдено") — защита от #Н/Д, когда значения нет в справочникеФайл со справочником и шестью формулами
Пять товаров и жёлтая ячейка выбора: меняете товар — все шесть примеров пересчитываются. Видно и поиск влево, и двумерный поиск по заголовкам.
Файл
indeks-poiskpoz.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Определите, что вернуть: выделите столбец с нужными данными — это первый аргумент ИНДЕКС.
- Определите, где искать: столбец, в котором лежит ключ. Он может стоять и правее, и левее — связке всё равно.
- Соберите:
=ИНДЕКС(столбец_ответа;ПОИСКПОЗ(ключ;столбец_поиска;0)). Ноль в конце обязателен. - Закрепите оба диапазона знаком доллара — иначе при протягивании они сползут.
- Нужен поиск и по строке, и по столбцу — поставьте второй ПОИСКПОЗ третьим аргументом ИНДЕКС: он найдёт нужный столбец по заголовку.
- Оберните в ЕСЛИОШИБКА, если значение может не найтись.
Где обычно ломается
- Забыли ноль в ПОИСКПОЗ. Без третьего аргумента функция ищет приблизительно и требует, чтобы диапазон был отсортирован по возрастанию. На несортированных данных вернёт соседнее значение — без всякой ошибки.
- Диапазоны разной длины.
ИНДЕКС(B6:B10;ПОИСКПОЗ(…;C6:C12;0))— ПОИСКПОЗ вернёт номер, которого в первом диапазоне нет, и получится#ССЫЛКА!. Оба диапазона должны начинаться с одной строки и быть одной высоты. #Н/Дпри видимом совпадении. Лишний пробел или число, записанное текстом. Прогоните ключ через СЖПРОБЕЛЫ и проверьте выравнивание: числа прижаты вправо, текст — влево.- Не закрепили диапазоны. При протягивании они уезжают вниз, и часть строк ищет мимо справочника. Доллары обязательны.
- ИНДЕКС с одним столбцом и номером столбца. Если первый аргумент — один столбец, третий аргумент не нужен: он есть только у двумерного диапазона.