ИНДЕКС и ПОИСКПОЗ с несколькими массивами

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

Как найти значение в нескольких массивах?

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

Общий синтаксис

=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)

Чтобы узнать лидера группы D из отдела A, скопируйте или введите приведенную ниже формулу в ячейку G5 и нажмите Enter, чтобы получить результат:

=ИНДЕКС(ВЫБОР())1,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),ПОИСКПОЗ(F5,$B$5:$B$8,0),2)

√ Примечание: знаки доллара ($) выше обозначают абсолютные ссылки, то есть диапазоны имен и классов в формуле не изменятся при перемещении или копировании формулы в другие ячейки. После ввода формулы перетащите маркер заполнения вниз, чтобы применить ее к нижестоящим ячейкам, и затем измените значение параметра array_numсоответственно.

Пояснение формулы

=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)

• CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Функция CHOOSE возвращает 1-й массив из трёх, перечисленных в формуле. Таким образом, она вернёт $B$5:$C$8, то есть диапазон данных отдела A.
• MATCH(F5,$B$5:$B$8,0): Параметр match_type 0 заставляет функцию MATCH вернуть позицию первого совпадения значения Group D, находящегося в ячейке F5, в массиве $B$5:$B$8, который представляет собой 4.
• INDEX()CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2) = INDEX($B$5:$C$8,4,2Функция INDEX извлекает значение на пересечении 4-й строки и 2-го столбца диапазона $B$5:$C$8, то есть Emily.

Чтобы избежать необходимости каждый раз изменять значение параметра array_numПри копировании формулы используйте вспомогательный столбец — столбец D. Формула будет выглядеть следующим образом:

=ИНДЕКС(ВЫБОР())D5,$B$5:$C$8 ,$B$11:$C$14 ,$B$17:$C$20),ПОИСКПОЗ(F5,$B$5:$B$8,0),2)

√ Примечание: числа 1,2,3во вспомогательном столбце обозначают массивы array1,array2,array3, указанные внутри функции ВЫБОР.

Связанные функции

Связанные формулы