INDICE y COINCIDIR con múltiples criterios

{= INDEX (range1, MATCH (1,(A1=range2)*(B1=range3)*(C1=range4),0))}

Para buscar valores con INDICE y COINCIDIR, utilizando varios criterios, puede utilizar una fórmula de matriz. En el ejemplo que se muestra, la fórmula en H8 es:

Nota: esta es una fórmula de matriz , y debe ingresarse con control + shift + enter, excepto en Excel 365 .

Esta es una fórmula más avanzada. Para conocer los conceptos básicos, consulte Cómo usar INDEX y MATCH .

redondear al millar más cercano sobresalir

Normalmente, una fórmula de COINCIDENCIA DE ÍNDICE se configura con COINCIDIR configurado para mirar a través de un rango de una columna y proporcionar una coincidencia basada en criterios dados. Sin concatenar valores en un columna de ayuda , o en la fórmula misma, no hay forma de proporcionar más de un criterio.

Esta fórmula soluciona esta limitación utilizando lógica booleana para crear un formación de unos y ceros para representar filas que coincidan con los 3 criterios, luego use COINCIDIR para que coincida con el primer 1 encontrado. La matriz temporal de unos y ceros se genera con este fragmento:

Aquí comparamos el artículo en H5 con todos los artículos, el tamaño en H6 con todos los tamaños y el color en H7 con todos los colores. El resultado inicial son tres matrices de resultados VERDADERO / FALSO como este:

Propina: use F9 para ver estos resultados . Simplemente seleccione una expresión en la barra de fórmulas y presione F9.

La operación matemática (multiplicación) transforma los valores VERDADERO FALSO en 1 y 0:

fórmula para convertir libra a kg

Después de la multiplicación, tenemos una única matriz como esta:

que se alimenta a la función COINCIDIR como la matriz de búsqueda, con un valor de búsqueda de 1:

En este punto, la fórmula es una fórmula estándar de COINCIDIR ÍNDICE. La función COINCIDIR devuelve 3 a ÍNDICE:

e INDICE devuelve un resultado final de $ 17,00.

Visualización de matrices

Las matrices explicadas anteriormente pueden ser difíciles de visualizar. La siguiente imagen muestra la idea básica. Las columnas B, C y D corresponden a los datos del ejemplo. La columna F se crea al multiplicar las tres columnas. Es la matriz entregada a MATCH.

Versión sin arreglo

Es posible agregar otro ÍNDICE a esta fórmula, evitando la necesidad de ingresar como una fórmula de matriz con control + shift + enter:

La función INDICE puede manejar matrices de forma nativa, por lo que el segundo INDICE se agrega solo para 'capturar' la matriz creada con la operación lógica booleana y devolver la misma matriz nuevamente a MATCH. Para hacer esto, INDICE se configura con cero filas y una columna. El truco de la fila cero hace que INDEX devuelva la columna 1 de la matriz (que de todos modos ya es una columna).

¿Por qué querrías la versión sin matriz? A veces, las personas olvidan ingresar una fórmula de matriz con control + shift + enter, y la fórmula devuelve un resultado incorrecto. Por lo tanto, una fórmula sin matriz es más 'a prueba de balas'. Sin embargo, la compensación es una fórmula más compleja.

Nota: en Excel 365 , no es necesario ingresar fórmulas de matriz de una manera especial.

cómo cambiar de negativo a positivo en Excel

INDICE y COINCIDIR con varios criterios.xlsx