Fórmula genérica
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Resumen
Para buscar un valor haciendo coincidir varias columnas, puede utilizar una fórmula de matriz basada en MMULT, TRANSPOSE, COLUMN e INDEX. En el ejemplo que se muestra, la fórmula en H4 es:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
donde "nombres" es el rango con nombre C4: E7, y "grupos" es el rango con nombre B4: B7. La fórmula devuelve el grupo al que pertenece cada nombre.
Nota: esta es una fórmula de matriz y debe ingresarse con control shift enter.
Explicación
Trabajando desde adentro hacia afuera, los criterios lógicos usados en esta fórmula son:
--(names=G4)
donde nombres es el rango con nombre C4: E7. Esto genera un resultado VERDADERO / FALSO para cada valor en los datos, y el doble negativo coacciona los valores VERDADERO FALSO a 1 y 0 para producir una matriz como esta:
(0,0,0;1,0,0;0,0,0;0,0,0)
Esta matriz consta de 4 filas por 3 columnas, coincidiendo con la estructura de "nombres".
Se crea una segunda matriz con esta expresión:
TRANSPOSE(COLUMN(names)^0))
La función COLUMNA se utiliza para crear una matriz numérica con 3 columnas y 1 fila, y TRANSPOSE convierte esta matriz en 1 columna y 3 filas. Elevar a la potencia de cero simplemente convierte todos los números de la matriz en 1. La función MMULT se utiliza para realizar la multiplicación de matrices:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
y el resultado entra en la función COINCIDIR como matriz, con 1 como valor de búsqueda:
MATCH(1,(0;1;0;0),0)
La función COINCIDIR devuelve la posición de la primera coincidencia, que corresponde a la fila de la primera fila coincidente que cumple los criterios proporcionados. Esto se introduce en INDEX como el número de fila, con el rango denominado "grupos" como la matriz:
=INDEX(groups,2)
Finalmente, INDICE devuelve "Bear", el grupo al que pertenece Adam.
Literal contiene para criterios
Para verificar valores de texto específicos en lugar de una coincidencia exacta, puede usar las funciones ISNUMBER y SEARCH juntas. Por ejemplo, para hacer coincidir las celdas que contienen "manzana", puede usar:
=ISNUMBER(SEARCH("apple",data))
Esta fórmula se explica aquí.
