Строка и столбец ВПР, программный код col_index_num

#excel

Вопрос:

Существует ли формула для поиска строки ВПР (обычный ВПР) условие для поиска столбца?

Например, строка, которую я просматриваю, — «ИТОГО», а столбец, который я хочу просмотреть, — «ИЮЛЬ 2020», это изменится в зависимости от ввода ячейки. Однако этот лист постоянно меняется и добавляются месяцы. Я не хочу жестко кодировать номер индекса col.

Буду признателен за любую помощь.

Спасибо.

1. INDEX/MATCH/MATCH .

2. Если вы всегда ищете последнее (самое правое) значение столбца, вы могли бы использовать: =OFFSET(C2,0,COUNTA(D2: Y2)), корректируя Y2, чтобы учесть максимальную ожидаемую букву столбца, возможно, плюс пару. Также предполагается, что не будет никаких пустых значений. Если возможны пустые значения, вы могли бы настроить COUNTA для подсчета строки заголовков столбцов вместо этого.

3. Извините, я всегда буду искать определенный месяц.

Ответ №1:

Всегда ли итоговое значение находится в одной строке? Если это так, то простая формула hlookup будет работать. Для диапазона таблицы просто расширяйте вправо настолько, насколько хотите.

Вот решение, если итоговое значение всегда находится в одной строке, например:

ИЗМЕНЕНИЯ, ВНЕСЕННЫЕ ПОСЛЕ ЗДЕСЬ

Если «итогом» могут быть разные строки (например, строка 4 или 5), то используйте формулу сопоставления индексов:

     =INDEX(E2:HDD4,MATCH(A5,D2:D5,0),MATCH(B4,E1:DD1,0))

Но я немного смущен тем, как общее количество может быть в строках 4 или 5. У вас все еще есть одна строка с надписью «итого»? Если у вас есть две строки для total, то, возможно, добавьте еще одну строку для «total» и сделайте ее самым нижним значением строк 4 и 5. Если это так, то вы могли бы придерживаться формулы hlookup.

Итак, в этом случае в строке 7 (totaltotal) создайте эту формулу массива для каждого столбца:

     =INDEX(E1:E5,MAX((E1:E5<>"")*(ROW(E1:E5))))

Обязательно нажмите CTRL-SHIFT-ENTER после ввода формулы, чтобы преобразовать ее в массив. Тогда ячейка B5 будет формулой hlookup из предыдущей, но на этот раз она будет ссылаться на строку 7 вместо строки 4.

     =HLOOKUP(B4,D:DD,7,FALSE)

Конечный результат: