Поиск влево с помощью ИНДЕКС и ПОИСКПОЗ
Чтобы найти информацию из функции ИНДЕКС в самом левом столбце электронной таблицы Excel о заданном элементе, расположенном в правой части, вы можете использовать комбинацию с ПОИСКПОЗ. Преимущество этой связки — возможность поиска значений в любом столбце, в отличие от другой мощной функции поиска Excel — ВПР.
Как выполнить поиск влево с помощью функций ИНДЕКС и ПОИСКПОЗ?
Чтобы заполнить информацию о товаре 30001, указанную в крайнем правом столбце данных, как показано на скриншоте выше, воспользуйтесь функциями ИНДЕКС и ПОИСКПОЗ: сначала ПОИСКПОЗ определит нужную строку по заданному артикулу товара, а затем ИНДЕКС извлечёт соответствующую информацию из этой же строки.
Общий синтаксис
=INDEX(return_range,MATCH(lookup_value,lookup_array,[match_type]),column_num)
- диапазон_возврата: Диапазон, из которого комбинированная формула должна возвращать данные. В данном случае — это диапазоны цвета, размера и цены.
- искомое_значение: Значение, которое функция ПОИСКПОЗ использует для определения позиции соответствующей информации. В данном случае — это заданный идентификатор товара.
- массив_поиска: Диапазон ячеек, в котором перечислены искомые значения. В данном случае — это диапазон идентификаторов товаров.
- тип_сопоставления: 1, 0 или -1.
1 или опущено (по умолчанию) — функция ПОИСКПОЗ найдёт наибольшее значение, которое меньше или равно искомому_значению. Значения в массиве_поиска должны быть расположены по возрастанию.
0 — функция ПОИСКПОЗ найдёт первое значение, точно равное искомому_значению. Значения в массиве_поиска могут располагаться в любом порядке.
-1 — функция ПОИСКПОЗ найдёт наименьшее значение, которое больше или равно искомому_значению. Значения в массиве_поиска должны быть расположены по убыванию. - номер_столбца: столбец в диапазоне_возврата, из которого вы хотите получить данные; номер_столбца можно опустить, если в диапазоне_возврата только один столбец.
Чтобы заполнить информацию о товаре 30001, скопируйте или введите приведённые ниже формулы в соответствующие ячейки и нажмите ENTER, чтобы получить результаты:
Цвет (ячейка H6)
=ИНДЕКС()B5:D9;ПОИСКПОЗ()H3;E5:E9;0);1)
Размер (ячейка H7)
=ИНДЕКС()B5:D9;ПОИСКПОЗ()H3;E5:E9;0);2)
Цена (ячейка H8)
=ИНДЕКС()B5:D9;ПОИСКПОЗ()H3;E5:E9;0);3)
√ Примечание: вместо ввода идентификатора товара 30001 в формулы мы использовали ссылку на ячейку H3, чтобы сделать формулы динамическими. Благодаря этому информацию о других товарах можно легко получить — достаточно просто изменить идентификатор товара в ячейке H3.
Или вы можете сузить диапазон_возвратадо одного столбца, выбрав только диапазон цвета, размера или цены. В этом случае формулы будут выглядеть так:
Цвет (ячейка H6)
=ИНДЕКС()B5:B9;ПОИСКПОЗ()H3;E5:E9;0))
Размер (ячейка H7)
=ИНДЕКС()C5:C9;ПОИСКПОЗ()H3;E5:E9;0))
Цена (ячейка H8)
=ИНДЕКС()D5:D9;ПОИСКПОЗ()H3;E5:E9;0))
√ Примечание: параметр номер_столбцаопущен, так как в диапазоне_возврататолько один столбец.
Пояснение формулы
В качестве примера возьмём следующую формулу:
=INDEX(B5:D9,MATCH(h3,E5:E9,0),2)
- MATCH(h3,E5:E9,0):тип_сопоставления 0указывает функции ПОИСКПОЗ найти точное совпадение значения 3001(из ячейки H3) в диапазоне E5:E9. Функция вернёт 1.
- ИНДЕКС()B5:D9,MATCH(h3,E5:E9,0),2) = ИНДЕКС(B5:D9Функция ИНДЕКС возвращает 1-е значение во 2-м столбце (столбце «размер») диапазона B5:D9, то есть Large.
Связанные функции
Функция ИНДЕКС Excel возвращает Отображаемое значение на основе заданной позиции из диапазона или массива.
Функция ПОИСКПОЗ Excel выполняет поиск определённого значения в диапазоне ячеек и возвращает относительную позицию этого значения.
Связанные формулы
Точное совпадение с помощью ИНДЕКС и ПОИСКПОЗ
Если вам нужно быстро найти в Excel информацию о конкретном товаре, фильме, человеке или чём-то ещё — используйте эффективную комбинацию функций ИНДЕКС и ПОИСКПОЗ.
Приблизительное совпадение с помощью ИНДЕКС и ПОИСКПОЗ
Иногда в Excel нужно находить приблизительные совпадения — например, чтобы оценить результаты работы сотрудников, выставить студентам оценки или рассчитать почтовые тарифы по весу. В этом руководстве мы покажем, как с помощью функций ИНДЕКС и ПОИСКПОЗ получить нужные результаты.
Поиск ближайшего совпадения по нескольким критериям
Иногда требуется найти ближайшее или приблизительное совпадение по нескольким критериям одновременно. Сочетание функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ позволяет быстро справиться с такой задачей в Excel.
Вы, вероятно, знаете, что для поиска по диапазону значений в Excel можно комбинировать функции ИНДЕКС и ПОИСКПОЗ или использовать ВПР. Однако эти методы не учитывают регистр символов. Чтобы выполнить поиск с учётом регистра, используйте функции ТОЧНО и ВЫБОР.
Лучшие инструменты для повышения продуктивности в Office
Kutools для Excel — Помогает Вам выделяться из толпы
Kutools для Excel предлагает более 300 функций,гарантируя, что всё необходимое всегда под рукой…
Office Tab — Включает вкладочное чтение и редактирование в Microsoft Office (включая Excel)
- Переключайтесь между десятками открытых документов всего за секунду!
- Сокращает сотни кликов мышью каждый день — забудьте о «мышечной руке» раз и навсегда!
- Повышает продуктивность на 50 % при одновременном просмотре и редактировании нескольких документов.
- Добавляет эффективность вкладок в Office (включая Excel) — как в Chrome, Edge и Firefox.