
Fórmula genérica
=MAX(INDEX(data,0,MATCH(column,header,0)))
Resumo
Para recuperar o valor máximo em um conjunto de dados, onde a coluna é variável, você pode usar INDEX e MATCH junto com a função MAX. No exemplo mostrado, a fórmula em J5 é:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
onde dados (B5: F15) e cabeçalho (B4: F4) são intervalos nomeados.
Explicação
Nota: Se você é novo em INDEX e MATCH, veja: Como usar INDEX e MATCH
Em uma configuração padrão, a função INDEX recupera um valor em uma determinada linha e coluna. Por exemplo, para obter o valor na linha 2 e coluna 3 em um determinado intervalo:
=INDEX(range,2,3) // get value at row 2, column 3
No entanto, INDEX tem um truque especial - a capacidade de recuperar colunas e linhas inteiras. A sintaxe envolve fornecer zero para o argumento "outro". Se você quiser uma coluna inteira, forneça a linha como zero. Se você quiser uma linha inteira, forneça a coluna como zero:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
No exemplo mostrado, queremos encontrar o valor máximo em uma determinada coluna. A diferença é que a coluna precisa ser variável para que possa ser facilmente alterada. Em F5, a fórmula é:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Trabalhando de dentro para fora, primeiro usamos a função MATCH para obter o "índice" da coluna solicitada na célula J4:
MATCH(J4,header,0) // get column index
Com "Verde" em J4, a função MATCH retorna 3, já que Verde é o terceiro valor no cabeçalho do intervalo nomeado . Depois que MATCH retorna um resultado, a fórmula pode ser simplificada para isto:
=MAX(INDEX(data,0,3))
Com zero fornecido como o número da linha, INDEX retorna todos os valores na coluna 3 dos dados do intervalo nomeado . O resultado é retornado à função MAX em uma matriz como esta:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
E MAX retorna o resultado final, 93.
Valor mínimo
Para obter o valor mínimo com uma coluna variável, você pode simplesmente substituir a função MAX pela função MIN. A fórmula em J6 é:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Com FILTRO
A nova função FILTER também pode ser usada para resolver este problema, uma vez que FILTER pode filtrar dados por linha ou por coluna. O truque é construir um filtro lógico que excluirá outras colunas. COUNTIF funciona bem neste caso, mas deve ser configurado "para trás", com J4 como o intervalo e cabeçalho para os critérios:
=MAX(FILTER(data,COUNTIF(J4,header)))
Após a execução de COUNTIF, temos:
=MAX(FILTER(data,(0,0,1,0,0)))
E FILTER fornece a 3ª coluna para MAX, o mesmo que a função INDEX acima.
Como alternativa a COUNTIF, você pode usar ISNUMBER + MATCH em seu lugar:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
A função MATCH é novamente configurada "para trás", de forma que tenhamos um array com 5 valores que servirão como filtro lógico. Após ISNUMBER e MATCH rodarem, temos:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
E FILTER novamente fornece a 3ª coluna para MAX.