¿Cómo encontrar un valor en Excel utilizando dos o varios criterios?

Imagine que trabaja con una tabla de ventas de frutas como la que se muestra a continuación. Es posible que necesite buscar el importe de ventas en función de varios criterios, como el tipo de fruta, la fecha de venta y el peso. Al utilizar fórmulas matriciales en Excel, podrá recuperar estos valores de forma eficiente, incluso al aplicar múltiples condiciones. Este método es flexible y se adapta perfectamente a conjuntos de datos en los que debe localizar un valor de celda que coincida con varios criterios simultáneamente.

Fórmula matricial 1: Buscar valor con dos o varios criterios en Excel

La estructura general de esta fórmula matricial es la siguiente:

{=ÍNDICE(matriz;COINCIDIR(1;(criterio1=rango_búsqueda1)*(criterio2=rango_búsqueda2)…*(criterion=rango_búsquedan);0))}

Por ejemplo, si desea buscar el importe de ventas del mangovendido el 9/3/2019, introduzca la siguiente fórmula en una celda vacía y pulse Ctrl+Mayús+Intropara confirmarla como fórmula matricial:

=ÍNDICE(F3:F22;COINCIDIR(1;(J3=B3:B22)*(J4=C3:C22);0))

Nota: En este ejemplo,

• F3:F22 es la columna «Importe» de la que desea recuperar el valor.
• B3:B22 es la columna «Fecha»; C3:C22 es la columna «Fruta».
• J3 es la fecha elegida como primer criterio; J4 es el nombre de la fruta utilizado como segundo criterio.

Añadir más criterios es sencillo. Por ejemplo, para buscar el importe de ventas del mango el 9/3/2019 con un peso de 211, añada la tercera condición tanto en COINCIDIR como en los rangos de búsqueda, tal como se muestra:

=ÍNDICE(F3:F22;COINCIDIR(1;(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22);0))

Después de introducir la fórmula, pulse nuevamente Ctrl+Mayús+Intro para confirmarla. El resultado será el importe de ventas que cumpla todos los criterios especificados.

Fórmula matricial 2: Buscar valor con dos o varios criterios en Excel mediante concatenación

Como alternativa, puede usar la concatenación en su fórmula para adoptar un enfoque diferente, especialmente si busca una estructura más compacta. La fórmula básica es:

=ÍNDICE(matriz;COINCIDIR(criterio1&criterio2…&criterioNrango_búsqueda1&rango_búsqueda2…&rango_búsquedan;0);0)

Por ejemplo, para recuperar el importe de ventas de una fruta con un peso de 242el 9/1/2019:

=ÍNDICE(F3:F22;COINCIDIR(J3&J4B3:B22&C3:C22;0);0)

Nota: Aquí,

• F3:F22 es la columna Importe; B3:B22 es la Fecha; E3:E22 es la columna Peso.
• J3 es la fecha; J5 es el valor de peso para sus criterios.

Para más de dos criterios, amplíe tanto los criterios como los rangos de búsqueda en el mismo orden:

=ÍNDICE(F3:F22;COINCIDIR(J3&J4&J5B3:B22&C3:C22&E3:E22;0);0)

Al igual que antes, pulse Ctrl+Mayús+Intro para obtener el resultado correcto.

Ambos métodos de fórmula matricial le permiten encontrar el primer valor que cumple todos sus criterios. Sin embargo, exigen que los rangos de celdas tengan el mismo tamaño y no devuelven varias coincidencias: solo recuperan la primera. Si no hay ninguna coincidencia, la fórmula devuelve un error #N/A. Si prefiere una fórmula que muestre todas las coincidencias, considere usar la función FILTER (consulte a continuación los detalles).

Algunos consejos prácticos y observaciones:

• Si trabaja con versiones más recientes de Excel (Microsoft 365, Excel 2021), puede utilizar fórmulas de matriz dinámica y la función FILTER para simplificar este proceso.
• Para evitar errores #N/A cuando no haya coincidencias, envuelva la fórmula en IFERROR, por ejemplo: =IFERROR(INDEX(F3:F22,MATCH(1,(J3=B3:B22)*(J4=C3:C22),0)),«Not found»).
• Compruebe cuidadosamente que las celdas de sus criterios de búsqueda no contengan espacios adicionales ni tipos de datos diferentes.
• Si recibe un error al pulsar únicamente Intro, asegúrese de usar Ctrl + Mayús + Intro para confirmarla como fórmula matricial (en Excel 2019 y versiones anteriores).

Además de usar fórmulas, Excel ofrece la función Filtro avanzado, que le permite filtrar y extraer todas las filas que cumplan dos o más criterios, mostrando los resultados coincidentes en otra ubicación. Este enfoque es especialmente útil cuando quiere ver todos los registros que cumplen sus condiciones especificadas, en lugar de obtener un único valor. A continuación se explica cómo utilizarlo:

1. Vaya a la pestaña Datos y seleccione Avanzado en el grupo Ordenar y filtrar para abrir el cuadro de diálogo Filtro avanzado.

2. En el cuadro de diálogo Filtro avanzado, realice la siguiente configuración:
(1) Seleccione Copiar a otra ubicación en la sección Acción.
(2) Para Rango de la lista, seleccione el rango que contiene los datos que desea filtrar ()A1:E21 en este ejemplo).
(3) Para Rango de criterios, seleccione el rango que contiene sus condiciones de filtro ()H1:J2 en este caso). Asegúrese de que los encabezados de este rango de criterios coincidan exactamente con los de su tabla de datos.
(4) En Copiar a, seleccione la primera celda en la que desee pegar los resultados filtrados ()H9 en este caso).

3. Haga clic en Aceptar para realizar el filtrado.

Las filas que cumplan todas las condiciones del rango de criterios se copiarán al área de destino que haya especificado. Esto resulta especialmente útil para revisar o generar informes de todos los registros que coincidan simultáneamente con varias condiciones de filtro.

Algunos consejos y precauciones:

• Asegúrese de que los encabezados del rango de criterios coincidan exactamente con los de su tabla principal de datos; de lo contrario, el filtro podría no funcionar correctamente.
• El filtro avanzado admite condiciones Y y O: al colocar criterios en la misma fila, se aplica la lógica Y (todos deben cumplirse); al distribuirlos en filas distintas, se aplica la lógica O (basta con que se cumpla uno).
• El filtro avanzado no se actualiza de forma dinámica al modificar los datos; deberá volver a aplicarlo tras actualizar los datos o los criterios.
• Tenga en cuenta que las celdas vacías en el rango de criterios se interpretan como «coincidir con cualquier valor» para ese campo.

En comparación con las soluciones basadas en fórmulas, el filtro avanzado es especialmente adecuado para extraer conjuntos de datos completos que coincidan con los criterios, en lugar de solo una celda. Sin embargo, no es adecuado para búsquedas en tiempo real ni actualizadas con frecuencia, ya que debe volver a ejecutar el filtro tras modificar los datos.

Si utiliza una versión reciente de Excel (Microsoft 365 o Excel 2021 y posteriores), la función FILTRAR ofrece una forma dinámica e intuitiva de extraer todos los valores que cumplan varios criterios. Esta solución es ideal para quienes necesitan que los resultados se actualicen automáticamente al modificar los datos o los criterios, sin tener que introducir fórmulas matriciales complejas.

1. En una celda vacía, introduce una fórmula similar a la siguiente:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

En esta fórmula:

• F3:F22 es su columna Importe.
• B3:B22 es la columna de fechas, comparada con la fecha en J3.
• C3:C22 es la columna Fruta, comparada con la fruta en J4.

Si desea añadir una tercera condición, como hacer coincidir la columna Peso (E3:E22)con un valor en J5, amplíe la fórmula:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Tras pulsar Intro, Excel mostrará todas las cantidades que cumplan todos los criterios. Si no hay ninguna coincidencia, la fórmula devolverá el error #CALC!, que puedes gestionar con SI.ERROR:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Ventajas:

• Los resultados se actualizan automáticamente al modificar sus datos o criterios.
• Las fórmulas son más sencillas de mantener y ampliar que las fórmulas matriciales antiguas.
• Devuelve todas las coincidencias, no solo el primer valor encontrado.

Limitación: Solo disponible en Microsoft 365, Excel 2021 o versiones posteriores. No compatible con versiones anteriores.