¿Cómo encontrar un valor con dos o más criterios en Excel?

Supongamos que tienes una tabla de ventas de frutas como se muestra en la captura de pantalla a continuación, y necesitas encontrar el valor de cantidad basado en varios criterios. Aquí, este método te presentará algunas fórmulas de matriz para encontrar valores basados en estos criterios dados fácilmente.

Fórmula de matriz 1: encontrar valor con dos o más criterios en Excel

La expresión básica de esta fórmula de matriz se muestra a continuación:

{=INDEX(array,MATCH(1,(criteria 1=lookup_array 1)*(criteria 2= lookup_array 2)…*(criteria n= lookup_array n),0))}

Por ejemplo, si deseas encontrar la cantidad de ventas de mango ocurrida el 9/3/2019, puedes ingresar la siguiente fórmula de matriz en una celda en blanco, y luego presionar las teclas Ctrl + Shift + Enter juntas.

=INDICE(F3:F22,COINCIDIR(1,(J3=B3:B22)*(J4=C3:C22),0))

Nota: En la fórmula anterior, F3:F22 es la columna Cantidad donde buscarás el valor, B3:B22 es la columna Fecha, C3:C22 es la columna Fruta, J3 es una fecha dada como primer criterio, J4 es el nombre de la fruta dado como segundo criterio.

De acuerdo con la expresión de la fórmula de matriz, puedes agregar fácilmente criterios según sea necesario. Por ejemplo, ahora estás buscando la cantidad de ventas de mango ocurrida el 9/3/2019, y el peso del mango es 211, puedes agregar los criterios y lookup_array en la sección COINCIDIR de la siguiente manera:

=INDICE(F3:F22,COINCIDIR(1,(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22),0))

Y presiona Ctrl + Shift + Enter para encontrar la cantidad de ventas.

Fórmula de matriz 2: encontrar valor con dos o más criterios en Excel

La expresión básica de esta fórmula de matriz se muestra a continuación:

=INDICE(matriz,COINCIDIR(criterio1 & criterio2… & criterioN, lookup_array1 & lookup_array2… & lookup_arrayN,0),0)

Por ejemplo, si deseas encontrar la cantidad de ventas de una fruta cuyo peso es 242 y ocurre el 9/1/2019, puedes ingresar la siguiente fórmula en una celda en blanco, y presionar las teclas Ctrl + Shift + Enter juntas.

=INDICE(F3:F22,COINCIDIR(J3&J4,B3:B22&C3:C22,0),0)

Nota: En la fórmula anterior, F3:F22 es la columna Cantidad donde buscarás el valor, B3:B22 es la columna Fecha, E3:E22 es la columna Peso, J3 es una fecha dada como primer criterio, J5 es el valor de peso dado como segundo criterio.

Si deseas encontrar un valor basado en tres o más criterios, puedes agregar fácilmente tus criterios y lookup_array en la sección COINCIDIR. Ten en cuenta que los criterios y lookup_array deben estar en el mismo orden.

Por ejemplo, si deseas encontrar la cantidad de ventas de pera con un peso de 242 y que ocurre el 9/1/2019, puedes agregar los criterios y lookup_array de la siguiente manera:

=INDICE(F3:F22,COINCIDIR(J3&J4&J5,B3:B22&C3:C22&E3:E22,0),0)

Y presiona Ctrl + Shift + Enter para calcular la cantidad de ventas.

Además de las fórmulas, también puedes aplicar la función Filtro Avanzado para encontrar todos los valores con dos o más criterios en Excel. Por favor, sigue los siguientes pasos:

1. Haz clic en Datos > Avanzado para habilitar la función Filtro Avanzado.

2. En el cuadro de diálogo Filtro Avanzado, por favor haz lo siguiente:
(1) Marca la opción Copiar a otra ubicación en la sección Acción ;
(2) En el cuadro Rango de lista selecciona el rango donde buscarás los valores (A1:E21 en mi caso);
(3) En el cuadro Rango de criterios selecciona el rango donde buscarás los valores por (H1:J2 en mi caso);
(4) En el cuadro Copiar a selecciona la primera celda del rango de destino donde colocarás las filas filtradas (H9 en mi caso).

3. Haz clic en el botón Aceptar.

Ahora las filas filtradas que coinciden con todos los criterios listados se copian y se colocan en el rango de destino. Ver captura de pantalla: