Использование INDEX/MATCH для возврата значения из набора таблиц

Question

Мне трудно писать формулы INDEX/MATCH . Мне нужна формула для просмотра нескольких строк, чтобы найти ту, которая соответствует моему значению поиска. У меня есть несколько заголовков для разных месяцев.

Текущая формула у меня в ячейке B2 :

=INDEX($D5:$M5,MATCH($B$1,$D$4:$M$4,0))

Как вы можете видеть, если я наберу January в B1 , B2 отобразит a . Если я наберу February я бы хотел, чтобы он отображал 99 .

Answer 1

Глядя на ваши предыдущие вопросы, похоже, что вы пытаетесь извлечь данные для определенного столбца с учетом двух критериев поиска - Месяц и Ветвь.

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

С вашим рабочим листом, измененным следующим образом,

введите следующую формулу в B3:

=INDEX(D4:D16,MATCH($B$1,$D$4:$D$16,0)+MATCH($B$2,$C$4:$C$16,0)-1)

Объяснение:

Вместо индексации в строке формула индексируется в требуемый столбец данных, т.е. D4:D16 .

Первая MATCH() находит соответствующую строку месяца, а вторая - смещение соответствующей ветви. -1 требуется для преобразования индекса на основе единицы, возвращаемого вторым MATCH() в смещение на основе нуля. (И да, MATCH($B$2,$C$4:$C$16,0)-1 можно было бы заменить на MATCH($B$2,$C$5:$C$16,0) , но проще разобраться с -1 .)

Заметки:

• То, как формула была написана, означает, что заполнение формулы справа извлекает значение следующего столбца. Вот почему D4:D16 является относительной ссылкой.
• В качестве альтернативы, чтобы получить данные другого столбца, просто замените D4:D16 соответствующей ссылкой. (Абсолютная ссылка, $D$4:$D$16 , должна оставаться неизменной, если используется один заголовок Month, как объясняется далее.)
• Несколько заголовков для каждого месяца не требуется, даже при извлечении данных из столбца, отличного от первого. Только первый столбец данных нуждается в одном или, альтернативно, вместо этого может использоваться объединенный заголовок. (Вот почему $D$4:$D$16 является абсолютной ссылкой.)

Answer 2

У моего ответа есть два варианта:

1. Использование набора данных показано на снимке экрана.

2. Решение с измененным набором данных.

Опция 1:

Я хотел бы предложить метод, который включает в себя простой прием для управления различными наборами данных в соответствии с месяцем.

Как это устроено:

• Вам нужно создать вспомогательную ячейку в ячейке B41 , которая отражает название месяца в ячейке B42 .
• Формула в ячейке B42:

=IF(OR(B41=1,B41=2,B41=3,B41=4,B41=5,B41,6),CHOOSE(B41,"January","February","March","April","May","June"))

• Формула в ячейке B43 , перетащите ее вниз.

=IF($B$41=1,INDEX($B34:$L34,MATCH(B$42,$B$33:$L$33,0)),IF($B$41=2,INDEX($B38:$L38,MATCH(B$42,$B$37:$L$37,0)),""))

Замечания:

• Вторая формула должна быть расширена, как только будут включены данные за оставшуюся часть месяца.

Вариант 2:

• Введите эту формулу в ячейку B51 и заполните ее

  =INDEX($B47:$G47,MATCH($B$50,$B$46:$G$46,0))

При необходимости измените ссылки на ячейки в формуле.