Fórmula genérica
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Resumen
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:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter, excepto en Excel 365.
Explicación
Esta es una fórmula más avanzada. Para conocer los conceptos básicos, consulte Cómo usar INDICE y COINCIDIR.
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 una columna auxiliar, o en la propia fórmula, no hay forma de proporcionar más de un criterio.
Esta fórmula soluciona esta limitación mediante el uso de lógica booleana para crear una matriz de unos y ceros para representar filas que coinciden con los 3 criterios, luego usa MATCH para hacer coincidir el primer 1 encontrado. La matriz temporal de unos y ceros se genera con este fragmento:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
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:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Consejo: utilice 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:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Después de la multiplicación, tenemos una única matriz como esta:
(0;0;1;0;0;0;0)
que se alimenta a la función COINCIDIR como la matriz de búsqueda, con un valor de búsqueda de 1:
MATCH(1,(0;0;1;0;0;0;0))
En este punto, la fórmula es una fórmula estándar de COINCIDIR ÍNDICE. La función COINCIDIR devuelve 3 a ÍNDICE:
=INDEX(E5:E11,3)
e INDEX 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 matriz
Es posible agregar otro ÍNDICE a esta fórmula, evitando la necesidad de ingresar como una fórmula de matriz con control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
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. Entonces, 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.
