Cómo hacer INDEX MATCH con múltiples criterios en Excel

Hace un par de años, trabajé en el análisis de una campaña de marketing en la que tenía que comparar el rendimiento de las ventas en varias regiones. Los datos estaban repartidos en varias hojas de Excel, y tenía que extraer cifras de ventas de productos concretos en un único informe resumido. Al principio, intenté buscar y copiar manualmente los datos, pero no fue tan fácil como pensaba. Si hay una fila equivocada, todo el informe podría venirse abajo.

Fue entonces cuando encontré INDEX MATCH(). Me costó unos cuantos intentos acertar con la fórmula, pero se convirtió en parte de mi rutina en cuanto vi lo fácil que era localizar y sacar las cifras exactas que necesitaba. Con sólo dos funciones, podía extraer exactamente los datos que necesitaba, por muy dispersos que estuvieran en las hojas de cálculo.

En este artículo, te explicaré cómo puedes hacer lo mismo utilizando las funciones INDEX() y MATCH(). Siempre hay algo más que aprender con Excel. Si eres principiante, te recomiendo encarecidamente nuestro curso Introducción a Excel. Si tienes más experiencia, prueba nuestro curso Funciones avanzadas de Excel.

Un repaso a INDEX MATCH

INDEX MATCH es una forma abreviada de hablar de la combinación de dos funciones de Excel que trabajan juntas para realizar búsquedas avanzadas. También podríamos referirnos a esta idea como INDEX(MATCH()), pero en este artículo elegiré INDEX MATCH. Ahora, veamos cada uno por separado:

La función INDEX() recupera el valor de una celda en función de su posición dentro de un rango especificado. Ésta es su sintaxis:

=INDEX(array, row_num, [column_num])

Toma:

• array es el rango de celdas del que quieres recuperar un valor.

• row_num es el número de fila de la matriz de la que hay que devolver un valor.

• column_num (opcional) es el número de columna de la matriz de la que hay que devolver un valor.

La función MATCH() identifica la posición relativa de un valor dentro de un intervalo. Su sintaxis es:

=MATCH(lookup_value, lookup_array, [match_type])

Toma:

• lookup_value es el valor que quieres encontrar.

• lookup_array es el rango en el que la función busca el valor.

match_type es opcional. 1 (por defecto) busca el valor menor o igual que lookup_value (la matriz debe estar ordenada de forma ascendente). 0 encuentra una coincidencia exacta (no es necesario ordenar la matriz). -1 encuentra el valor más pequeño mayor o igual que lookup_value (la matriz debe estar ordenada de forma descendente).

Cómo combinar INDEX() con MATCH()

Anidando MATCH() dentro de INDEX(), podemos crear una búsqueda dinámica. Vamos a entenderlo con un ejemplo: Supongamos que quieres encontrar la posición de David Wilson en el conjunto de datos. En lugar de codificar el número de fila en INDEX(), utiliza MATCH() para determinarlo:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

En la fórmula anterior, MATCH("David Wilson", A2:A6, 0) devuelve 4, que es la posición de la fila. Y INDEX(C2:C6, 4) recupera el valor de la 4ª fila del rango C2:C6, que es Seattle.

Combina INDEX() con MATCH(). Imagen del autor.

Para hacerlo aún más dinámico, puedes sustituir el código duro de David Wilson por una referencia de celda. De esta forma, la fórmula se ajusta automáticamente en función del valor de D4:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Sustituye el valor codificado en el INDEX MATCH. Imagen del autor.

PARTIDO ÍNDICE vs. VLOOKUP()

Ahora que sabes cómo funcionan INDEX() y MATCH() individualmente y cómo su combinación hace que las búsquedas sean más dinámicas, veamos por qué INDEX MATCH es mejor opción que VLOOKUP().

• A diferencia de VLOOKUP(),, que requiere que la columna de búsqueda esté a la izquierda, INDEX MATCH te permite recuperar datos de cualquier columna, independientemente de su posición.

• INDEX MATCH sólo procesa el rango de celdas necesario, en comparación con VLOOKUP(),, que escanea tablas enteras. 

• Las fórmulas que utilizan VLOOKUP() pueden romperse si se insertan o eliminan columnas, ya que dependen de índices de columna estáticos. Por otra parte, INDEX MATCH hace referencia a rangos dinámicos para garantizar que tus fórmulas permanezcan intactas a pesar de los cambios estructurales en tus datos.

• Con INDEX MATCH, no tenemos que contar manualmente los números de las columnas. Especifica la columna de búsqueda y la columna de retorno, y ya está. 

INDEX MATCH con criterios múltiples

A menudo tengo que trabajar con conjuntos de datos que contienen entradas duplicadas, y encontrar valores en ellos es extremadamente difícil. Pero ahora utilizo INDEX MATCH porque maneja estos escenarios muy fácilmente, a diferencia de otras fórmulas de búsqueda estándar. Deja que te explique cómo lo utilizo paso a paso.

Configurar datos para múltiples criterios

Primero, crea tu conjunto de datos y asegúrate de que está bien organizado en una tabla con encabezados claros para cada columna. Cada fila debe representar un registro único, y cada columna debe contener un atributo de datos específico.

Por ejemplo, aquí tienes un conjunto de datos de muestra:

Conjunto de datos para criterios múltiples INDEX MATCH. Imagen del autor.

Escribe la fórmula para criterios múltiples

Una vez que tus datos estén bien organizados, es hora de escribir la fórmula. La fórmula INDEX MATCH recupera un valor de otra columna identificando una fila que cumpla varias condiciones. Esto se hace combinando pruebas lógicas dentro de la función MATCH() e incrustándola dentro de la función INDEX().

Esta es la sintaxis básica: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Toma: 

• Return_range es el rango a partir del cual se devolverá el valor.

• Criteria1, Criteria2, … son las condiciones que deben cumplirse.

• Range1, Range2, … son rangos correspondientes a los criterios.

Ahora que tenemos los datos configurados, vamos a examinar detenidamente los dos métodos para responder a nuestra pregunta: cómo utilizar INDEX MATCH con varios criterios.

Utilizar columnas de ayuda para criterios complejos

Si tu conjunto de datos tiene varias condiciones, utiliza columnas de ayuda para simplificar el proceso. Combinará todas las condiciones en una sola columna para facilitar las búsquedas. Por ejemplo, estoy utilizando el mismo conjunto de datos para crear una columna de ayuda combinando las columnas Nombre y Salario:

=A2&B2

Crea una columna de ayuda. Imagen del autor.

Esta columna de ayuda simplifica mi fórmula INDEX MATCH. En lugar de escribir una fórmula de matriz compleja con múltiples condiciones, hice referencia a la columna de ayuda en mi fórmula para obtener un enfoque más sencillo:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH con columna de ayuda. Imagen del autor.

Combinar varios criterios con una fórmula de matriz

Si no prefieres las columnas de ayuda, puedes utilizar fórmulas de matriz para conseguir el mismo resultado. Te permiten evaluar múltiples criterios directamente dentro de la función MATCH(). Por ejemplo, así es como encuentro El salario de Alice en el departamentode RRHH:

Paso 1: Escribe la función MATCH() con condiciones lógicas:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

En esta fórmula, 1 garantiza que la función MATCH() busque filas en las que todas las condiciones sean verdaderas. (F4=A2:A11) comprueba si el valor de F4 coincide con cualquier valor del intervalo A2:A11. (F5=B2:B11) comprueba si el valor de F5 coincide con cualquier valor del intervalo B2:B11. El operador * actúa como una lógica AND, asegurándose de que se cumplen todas las condiciones.

Paso 2: Envuelve esta función MATCH() dentro de la función INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Paso 3: Finaliza la fórmula. Si utilizas una versión antigua de Excel, pulsa Ctrl+Mayús+Intropara convertirla en una fórmula de matriz. En versiones más recientes, pulsa Intro.

Matriz INDEX MATCH con criterios múltiples. Imagen del autor.

Usos avanzados de INDEX MATCH con criterios múltiples

Puedes hacer mucho más con la función INDEX MATCH. Veamos cómo: 

Utiliza INDEX MATCH con rangos con nombre y rangos dinámicos

Utilizo rangos con nombre en Excel para establecer nombres significativos como resultados o totalVentas en lugar de referencias estándar como A1:A10. De este modo, es más fácil gestionar las fórmulas en distintas hojas. 

Para dar nombre a un rango de celdas, selecciona las celdas y pulsa Ctrl + F3 (para Windows) o Cmd + F3 (para Mac) para abrir el Administrador de nombres. A continuación, haz clic en Nuevo, introduce un nombre y haz clic en Aceptar.

Nombra el rango. Imagen del autor.

La única diferencia entre un rango con nombre y un rango dinámico es que un rango con nombre se refiere a un grupo fijo de celdas, mientras que un rango dinámico se ajusta automáticamente cuando se añaden o eliminan datos.

Para establecer un rango dinámico, selecciona las celdas. Enla pestaña Fórmulas, haz clic en Administrador denombres o pulsa Ctrl + F3 para abrir el Administrador de nombres deExcel y haz clic en Nuevo. Aparecerá el cuadro de diálogo Nuevo nombre. Ahora, en el campo Nombre , introduce el nombre que desees. A continuación, en elcampo Se refiere a , introduce la fórmula para el rango dinámico .

Establece un rango dinámico. Imagen del autor. 

Veamos ahora un ejemplo: He definido dos rangos dinámicos y uno estático:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Ahora, utilizo estos rangos dentro de la fórmula INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

Y puedes ver que la fórmula es mucho más fácil de entender con nombres claros.

Utiliza rangos dinámicos y con nombre con INDEX MATCH. Imagen del autor.

INDEX MATCH anidado para búsquedas complejas

Aparte del trabajo básico, también puedes utilizar funciones anidadas de INDEX MATCH para realizar búsquedas complejas. Por ejemplo, tengo un conjunto de datos que muestra las ventas por categoría de producto en diferentes regiones. 

Conjunto de datos en bruto. Imagen del autor.

Quiero encontrar ventas de muebles en el Este. Pero para ello tengo que hacer coincidir tanto la categoría de producto (fila) como la región (columna), cosa que no puede hacer un INDEX MATCH básico. Por eso utilizo aquí la siguiente fórmula anidada INDEX MATCH:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Funciona así: El INDEX() extrae un valor del rango B2:D4, pero necesita un número de fila y un número de columna para saber exactamente dónde buscar. Así, la primera MATCH(D6, A2:A4, 0) calcula el número de fila. Si D6contiene Muebles, busca en la columna A2:A4 y encuéntralo en la segunda fila .

A continuación, MATCH(D7, B1:D1, 0) determina el número de columna. Si D7 dice Este, busca a través de B1:D1 y lo encuentra en la segunda columna .

Una vez que INDEX() conoce la fila y la columna, muestra los valores de salida. En nuestro caso, las ventas de Muebles en Oriente son 450.

Utiliza INDEX MATCH anidado. Imagen del autor.

Utilizo esta fórmula en lugar de buscar manualmente en filas y columnas porque lo maneja todo con precisión. 

Desafíos comunes y consejos para solucionar problemas

Cuando empecé a utilizar INDEX MATCH, me encontré con varios problemas, y no quiero que tú experimentes las mismas frustraciones. Por ello, te guiaré a través de los retos más comunes y te mostraré cómo superarlos.

Gestión de errores en las fórmulas INDEX MATCH

Errores como #N/A y #VALUE! pueden parecer frustrantes inicialmente, pero son bastante fáciles de solucionar. Veamos cómo detectar la causa del problema y los pasos sencillos para solucionarlo.

El error #N/A se produce cuando la función MATCH() no encuentra un valor. Esto se debe a que el valor de búsqueda no existe en la matriz de búsqueda, o los datos contienen espacios ocultos. Por ejemplo, una vez hice referencia a la columna equivocada al extraer Nombres de empleados:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

#N/A error in INDEX MATCH. Imagen del autor.

Para solucionar estos problemas, confirma que el valor de búsqueda existe en la matriz y utiliza la función TRIM() para limpiar los espacios:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

Error #N/A corregido en INDEX MATCH. Imagen del autor.

#VALUE! aparece cuando la fórmula no está configurada como fórmula de matriz. Por ejemplo, si utilizo la función MATCH() e incluyo más de un rango, Excel lo ve como una fórmula de matriz. Sin embargo, si no se configura correctamente, Excel arrojará un error #VALUE!.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

#Error de valor en INDEX MATCH. Imagen del autor.

Para resolverlo, pulsa Ctrl + Mayús + Intro después de introducir la fórmula. De esta forma, Excel envolverá la fórmula entre llaves {}, indicando que ahora es una fórmula de matriz. Pero no escribas las llaves manualmente porque se rompería la fórmula.

#Error de valor corregido en INDEX MATCH. Imagen del autor.

Optimizar el rendimiento con grandes conjuntos de datos

En conjuntos de datos más grandes, mis fórmulas se ralentizaban de vez en cuando y, por ello, tenía que esperar a que se actualizaran los cálculos. Si tú también tienes problemas similares, prueba estos consejos: 

• Limita el rango de búsqueda: Limita las gamas a lo estrictamente necesario. Por ejemplo, en lugar de A:A, utiliza A1:A100 para reducir el tiempo de cálculo.

• Utiliza columnas de ayuda: Precalcular criterios complejos con columnas de ayuda. Esto reducirá la carga computacional de las fórmulas de matriz.

• Activa el modo de cálculo manual: Cambia Excel al modo de cálculo manual para evitar recálculos constantes. Después de hacer cambios, pulsa F9 para actualizar las fórmulas manualmente.

• Evita las funciones volátiles: Minimiza utilizando funciones volátiles como NOW(), RAND(), y TODAY() en combinación con INDEX MATCH. Estas funciones lanzan recálculos cada vez que se actualiza el libro de trabajo.

Reflexiones finales 

INDEX MATCH ahorran tiempo y simplifican el análisis de datos complejos. Si trabajas con conjuntos de datos masivos, puede merecer la pena probarlos. Pero la mejor forma de consolidar tu comprensión es mediante la práctica. Así que yo diría que abordes unos cuantos conjuntos de datos y experimentes con lo que has aprendido. Así es como afilé mis habilidades. 

Para profundizar en tus conocimientos, consulta nuestro curso Funciones avanzadas de Excel para dominar una gama más amplia de potentes herramientas. Pero si quieres adquirir una amplia experiencia en el análisis de datos en Excel, te recomiendo nuestro curso Análisis de datos en Excel. Abarca desde la preparación de los datos hasta su visualización.