EXCEL: Indice y combine en varias columnas

Para buscar un valor haciendo coincidir varias columnas, puede usar una fórmula de matriz basada en MMULT, TRANSPONER, COLUMNA e INDICE. En el ejemplo que se muestra, la fórmula en H4 es:

{=INDICE(groups,COINCIDIR(1,MMULT(--(names=G4),TRANSPONER(COLUMNA(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.

Cómo funciona esta fórmula

Trabajando de adentro hacia afuera, el criterio lógico utilizado en esta fórmula es:

--(names=G4)

donde names es el rango con nombre C4: E7. Esto genera un resultado VERDADERO / FALSO para cada valor en los datos, y el doble negativo obliga a 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 es de 4 filas por 3 columnas, que coinciden con la estructura de los "nombres".

Se crea una segunda matriz con esta expresión:

TRANSPONER(COLUMNA(names)^0))

La función COLUMNA se usa para crear una matriz numérica con 3 columnas y 1 fila, y TRANSPONER convierte esta matriz en 1 columna y 3 filas. Elevar a la potencia de cero simplemente convierte todos los números en la matriz a 1. La función MMULT se usa 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:

COINCIDIR(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 INDICE como el número de fila, con el rango "grupos" con nombre como la matriz:

=INDICE(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 ESNUMERO y HALLAR juntas. Por ejemplo, para hacer coincidir las celdas que contienen "manzana" puede usar:

=ESNUMERO(HALLAR("apple",data))

Esta fórmula se explica aquí.