Fórmula genérica
=MAX(INDEX(data,0,MATCH(column,header,0)))
Resumen
Para recuperar el valor máximo en un conjunto de datos, donde la columna es variable, puede usar INDICE y COINCIDIR junto con la función MAX. En el ejemplo que se muestra, la fórmula en J5 es:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
donde los datos (B5: F15) y el encabezado (B4: F4) son rangos con nombre.
Explicación
Nota: Si es nuevo en INDEX y MATCH, consulte: Cómo usar INDEX y MATCH
En una configuración estándar, la función INDICE recupera un valor en una fila y columna determinadas. Por ejemplo, para obtener el valor en la fila 2 y la columna 3 en un rango determinado:
=INDEX(range,2,3) // get value at row 2, column 3
Sin embargo, INDEX tiene un truco especial: la capacidad de recuperar columnas y filas completas. La sintaxis implica proporcionar cero para el argumento "otro". Si desea una columna completa, proporcione la fila como cero. Si desea una fila completa, proporcione la columna como cero:
=INDEX(data,0,n) // retrieve column n =INDEX(data,n,0) // retrieve row n
En el ejemplo que se muestra, queremos encontrar el valor máximo en una columna dada. El giro es que la columna debe ser variable para que se pueda cambiar fácilmente. En F5, la fórmula es:
=MAX(INDEX(data,0,MATCH(J4,header,0)))
Trabajando de adentro hacia afuera, primero usamos la función COINCIDIR para obtener el "índice" de la columna solicitada en la celda J4:
MATCH(J4,header,0) // get column index
Con "Verde" en J4, la función COINCIDIR devuelve 3, ya que Verde es el tercer valor en el encabezado del rango con nombre . Después de que COINCIDIR devuelva un resultado, la fórmula se puede simplificar a esto:
=MAX(INDEX(data,0,3))
Si se proporciona cero como número de fila, INDICE devuelve todos los valores de la columna 3 de los datos del rango con nombre . El resultado se devuelve a la función MAX en una matriz como esta:
=MAX((83;54;35;17;85;16;70;72;65;93;91))
Y MAX devuelve el resultado final, 93.
Valor mínimo
Para obtener el valor mínimo con una columna variable, simplemente puede reemplazar la función MAX con la función MIN. La fórmula en J6 es:
=MIN(INDEX(data,0,MATCH(J4,header,0)))
Con FILTRO
La nueva función FILTRO también se puede utilizar para resolver este problema, ya que FILTRO puede filtrar datos por fila o por columna. El truco consiste en construir un filtro lógico que excluya otras columnas. COUNTIF funciona bien en este caso, pero debe configurarse "al revés", con J4 como rango y encabezado para los criterios:
=MAX(FILTER(data,COUNTIF(J4,header)))
Después de que se ejecute COUNTIF, tenemos:
=MAX(FILTER(data,(0,0,1,0,0)))
Y FILTRO entrega la 3ª columna a MAX, igual que la función INDICE anterior.
Como alternativa a COUNTIF, puede usar ISNUMBER + MATCH en su lugar:
=MAX(FILTER(data,ISNUMBER(MATCH(header,J4,0))))
La función COINCIDIR se configura nuevamente "al revés", de modo que obtengamos una matriz con 5 valores que servirá como filtro lógico. Después de ejecutar ISNUMBER y MATCH, tenemos:
=MAX(FILTER(data,(FALSE,FALSE,TRUE,FALSE,FALSE)))
Y FILTER vuelve a entregar la tercera columna a MAX.
