Búsquedas con varios criterios con INDEX-MATCH

A veces una sola columna no identifica una fila de forma única. Puede necesitar el precio de un producto en un tamaño específico o el salario de un empleado en un departamento concreto.

Para eso se utiliza una búsqueda con varios criterios: buscar en dos o más columnas a la vez para localizar exactamente una fila.

INDEX-MATCH lo resuelve de forma elegante combinando las condiciones en una sola prueba de coincidencia, sin necesidad de columnas auxiliares.

El modelo mental más sencillo consiste en unir las columnas clave en una sola. Añada una columna auxiliar que concatene el producto y el tamaño, y después haga una búsqueda normal en ella.

Por ejemplo, una celda auxiliar podría contener =A2&"|"&B2, lo que produciría "Shirt|Large". Después, usaría MATCH para buscar "Shirt|Large" en esa columna combinada.

Este método funciona, pero llena la hoja de datos innecesariamente. En las siguientes escenas verá cómo prescindir por completo de la columna auxiliar.

El truco principal consiste en multiplicar las dos pruebas de condición dentro de MATCH.

(A2:A10=G1) produce una matriz de valores TRUE/FALSE para el primer criterio. (B2:B10=G2) hace lo mismo para el segundo. Al multiplicarlas, (A2:A10=G1)*(B2:B10=G2) genera 1 solo donde ambas son TRUE y 0 en el resto.

Después, MATCH busca el valor 1 para encontrar la fila que cumple ambas condiciones.

En las hojas de cálculo, TRUE se comporta como 1 y FALSE como 0. Multiplicar dos de estos valores imita un AND lógico:

• 1 por 1 = 1 (se cumplen ambas condiciones)
• 1 por 0 = 0
• 0 por 1 = 0
• 0 por 0 = 0

Por tanto, solo las filas que cumplen ambos criterios producen un 1. Todas las demás se convierten en 0. Ese único 1 marca la fila que queremos.

Ahora envuelva la matriz multiplicada en MATCH y busque el valor exacto 1.

MATCH(1, (A2:A10=G1)*(B2:B10=G2), 0) devuelve la posición de la primera fila en la que ambas condiciones son TRUE.

Si la combinación coincidente se encuentra en la cuarta fila de datos, MATCH devuelve 4. Esa es la posición que INDEX necesita para obtener el resultado.

Pase el resultado de MATCH a INDEX sobre la columna que realmente desea, por ejemplo, el precio de C2:C10.

La fórmula completa significa: devolver, desde C2:C10, el valor de la fila cuyo producto sea igual a G1 y cuyo tamaño sea igual a G2.

Esta es una verdadera búsqueda con varios criterios, sin columna auxiliar ni necesidad de reorganizar los datos.

Esta fórmula evalúa matrices de condiciones. En Excel moderno y Google Sheets, solo tiene que pulsar Enter para que funcione.

En versiones antiguas de Excel (anteriores a las matrices dinámicas), debe confirmarla como fórmula matricial con Ctrl+Shift+Enter, lo que añade llaves. Si el resultado es incorrecto o aparece un error en una versión antigua de Excel, normalmente falta realizar ese paso de confirmación.

¿Necesita tres criterios? Solo tiene que multiplicar otra prueba. Suponga que también desea comparar un color de la columna D con el valor de entrada G3.

Cada factor adicional (range=criterion) restringe aún más el resultado. Solo las filas en las que todas las condiciones sean TRUE conservan un producto igual a 1; cualquier valor FALSE convierte todo el producto en 0.

Este patrón se puede ampliar a tantas columnas como necesite.

Datos: A = producto, B = tamaño, C = precio. Desea obtener el precio de una "Shirt" en tamaño "Large".

• G1 = "Shirt", G2 = "Large".
• Las matrices de condiciones producen un 1 únicamente en la fila Shirt+Large, por ejemplo, la fila 4.
• MATCH(1, ..., 0) devuelve 4.
• INDEX(C2:C10, 4) devuelve el precio de esa fila.

Cambie cualquiera de las entradas y la fórmula volverá a encontrar al instante la fila correcta.

Tenga en cuenta lo siguiente:

• Rangos iguales: todos los rangos de condiciones y la columna de INDEX deben tener la misma altura.
• Ninguna coincidencia: si ninguna fila cumple todos los criterios, MATCH devuelve #N/A. Envuélvalo todo en IFERROR.
• Duplicados: si coincide más de una fila, MATCH solo devuelve la primera. Haga que sus criterios sean lo bastante específicos para obtener una coincidencia única.

Si pueden coincidir varias filas y prefiere sumar sus valores en lugar de obtener uno solo, SUMPRODUCT es una alternativa sencilla a INDEX-MATCH introducido como fórmula matricial.

Multiplica las matrices de condiciones por la columna de valores y suma los resultados, de modo que solo contribuyen las filas que cumplen ambos criterios. No es necesario pulsar Ctrl+Shift+Enter, porque SUMPRODUCT gestiona las matrices de forma nativa.

Use INDEX-MATCH para obtener un único valor coincidente y SUMPRODUCT para agregar todos los valores coincidentes.

Ponga a prueba sus conocimientos sobre las búsquedas con varios criterios.

Para búsquedas con varios criterios mediante INDEX-MATCH:

• Multiplique entre sí las matrices de condiciones: (A=G1)*(B=G2) da 1 únicamente donde se cumplen todas (un AND lógico).
• MATCH(1, ..., 0) encuentra la posición de esa fila.
• INDEX(returnCol, position) devuelve el valor.

Añada más factores *(range=criterion) para incluir condiciones adicionales, mantenga los rangos con la misma altura, confirme con Ctrl+Shift+Enter en versiones antiguas de Excel y use IFERROR como protección.