Индекс Excel / формула соответствия, но диапазон индекса меняется ежемесячно

Question

Моя формула для получения значения "Общий доход" для каждой компании:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

Работает красиво. Никаких проблем. ЛЮБИТЕ Индекс / Матч.

Проблема в том, что ссылка на диапазон индекса $H$19:$ZZ$19 может измениться на строку 20 в следующем месяце, если будут добавлены новые строки (каждый месяц вставляется новый отчет, поэтому он не знает, что ячейки были перемещены). Если бы я делал это каждый месяц, достаточно просто найти и заменить все, и готово, но я нет, а другие пользователи не могут.

Поэтому я стараюсь, чтобы это работало каждый месяц, несмотря ни на что. Я нашел способ вернуть номер строки с помощью Match("Total Income"...) , но есть ли способ найти текстовую строку "Total Income", чтобы получить номер строки войти в мою формулу индекса?

Так как строка соответствия работала, возвращая номер строки, я даже пытался поместить это уравнение в диапазон индекса (...$ H $ MATCH ("Общий доход", "Ежемесячно по классу"!D:D, 0). Это не сработало, как ожидалось, но я просто хотел, чтобы он поместил туда строку 19. Должен быть способ.

^{Индексный файл}

Answer 1

Я даже пытался поместить это уравнение в диапазон индекса (...$ H $ MATCH ("Общий доход", "Ежемесячно по классу"!D:D, 0). Это не сработало, как ожидалось, но я просто хотел, чтобы он поместил туда строку 19. Должен быть способ.

Это действительно так, и у тебя это было. Вам просто нужно использовать INDIRECT любое время, когда вы хотите использовать формулу для генерации ссылки на ячейку, в отличие от простого удаления формулы в середине ссылки на ячейку. Это великолепно! INDIRECT использует двойные кавычки для построения ссылки на ячейку в виде текста.

Так что, если это вся ваша, действующая формула:

=IFERROR(INDEX(YTD2016!$H$19:$ZZ$19,MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

Ваша текущая жестко заданная ссылка на ячейку для диапазона INDEX :

YTD2016!$H$19:$ZZ$19

И мы просто хотим заменить номера строк простым MATCH .

Давайте посмотрим на это так

YTD2016!$H$ 19 :$ZZ$ 19

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

YTD2016!$H$ MATCH("Total Income",'Monthly by Class'!D:D,0) :$ZZ$ MATCH("Total Income",'Monthly by Class'!D:D,0)

Оберните его с помощью INDIRECT и используйте кавычки и амперсанды для построения текстовой строки

INDIRECT( "YTD2016!$H$"& MATCH("Total Income",'Monthly by Class'!D:D,0) &":$ZZ$"& MATCH("Total Income",'Monthly by Class'!D:D,0) )

Положил все это вместе

INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0))

и вернуться на свое место в общей функции

=IFERROR(INDEX(INDIRECT("YTD2016!$H$"&MATCH("Total Income",'Monthly by Class'!D:D,0)&":$ZZ$"&MATCH("Total Income",'Monthly by Class'!D:D,0)),MATCH(RECAP!$A14,YTD2016!$H$4:$ZZ$4,0)),0)

..и Роберт брат твоей матери.

Answer 2

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

В качестве примера,

=ADDRESS(1,1)

вернул бы $ 1 $

вы бы указали соответствие в качестве первого аргумента, а номер столбца - в качестве второго аргумента, а затем поместили все это в свой индексный вызов.

Я надеюсь, что это помогает