Excel

Indexar y hacer coincidir en varias columnas

Index Match Multiple Columns

Fórmula de Excel: indexar y hacer coincidir en varias columnasFó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 un fórmula de matriz basado en MMULT, TRANSPOSE, COLUMN e INDEX. En el ejemplo que se muestra, la fórmula en H4 es:





|_+_|

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.

eliminar celdas en blanco en Excel Mac

Nota: esta es una fórmula de matriz y se debe ingresar con control shift enter.





Explicación

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

|_+_|

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:



|_+_|

Esta matriz tiene 4 filas por 3 columnas, coincidiendo con la estructura de 'nombres'.

Se crea una segunda matriz con esta expresión:

|_+_|

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:

atajo de teclado para eliminar la fila en Excel
|_+_|

y el resultado va a la función COINCIDIR como matriz, con 1 como valor de búsqueda:

cómo usar f11 en mac
|_+_|

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 alimenta en INDICE como el número de fila, con el rango denominado 'grupos' como la matriz:

|_+_|

Finalmente, INDEX 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:

|_+_|

Esta fórmula se explica aquí .

Autor Dave Bruns


^