Preparación de datos con INDEX-MATCH en hojas de cálculo

Normalmente, cuando alguien aprende a usar Excel por primera vez, lo más probable es que le presenten VLOOKUP como el método principal para preparar y combinar datos. VLOOKUP es, de hecho, una función potente que resuelve muchas tareas relacionadas con el manejo de datos, como se muestra en Data Wrangling with VLOOKUP in Spreadsheets. En ese mismo tutorial, también vimos los fundamentos de la combinación INDEX-MATCH, otro método para hacer búsquedas verticales en tablas que ofrece varias ventajas sobre VLOOKUP, a cambio de una fórmula algo más compleja.

Estas ventajas incluyen características como:

• Referencias de columna dinámicas: Te permiten insertar y eliminar columnas con seguridad en tu conjunto de datos sin que la función de búsqueda se vea afectada.
• Mayor velocidad en conjuntos de datos grandes: Si tus tablas son extensas, seguramente notarás que INDEX-MATCH es mucho más rápido que VLOOKUP, ya que INDEX-MATCH solo tiene en cuenta la columna de búsqueda y la columna de la que quieres recuperar el valor.
• Ubicación de los valores de búsqueda: VLOOKUP no puede buscar valores situados a la izquierda de la primera columna que selecciones en el rango de la tabla, mientras que INDEX-MATCH también puede buscar en horizontal, así que no tiene esa limitación.
• Tamaño del valor de búsqueda: VLOOKUP no puede buscar valores de más de 255 caracteres, pero INDEX-MATCH sí.

Además, INDEX-MATCH permite hacer búsquedas basadas en varias columnas y búsquedas que distinguen entre mayúsculas y minúsculas, algo muy útil cuando trabajas con ciertos IDs que pueden diferir por el uso de mayúsculas. Por eso, en este tutorial vamos a profundizar en las funciones avanzadas de la combinación INDEX-MATCH y a mostrar ejemplos de cómo pueden ayudarte en la práctica.

Los fundamentos de INDEX-MATCH

En su forma más sencilla, INDEX-MATCH puede usarse igual que VLOOKUP para realizar búsquedas verticales simples basadas en una clave común. La estructura básica de la fórmula es la siguiente:

=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))

• column_range: El rango de la columna cuyo valor quieres recuperar.
• lookup_value: El valor que quieres buscar (la clave).
• lookup_column_range: El rango de la columna que contiene las claves que quieres hacer coincidir.
• match_type: 0 o 1. Con 0 indicas coincidencia exacta y con 1 coincidencia aproximada. Es análogo al indicador FALSO/VERDADERO de VLOOKUP.

A continuación, vamos a ilustrar cómo funciona INDEX-MATCH usando un par de tablas de ejemplo. La primera tabla contiene algunos nombres y sus claves únicas. La segunda tabla contiene esas mismas claves (no necesariamente en el mismo orden) y unas descripciones. La tarea consiste en unir las descripciones de la segunda tabla con los nombres de la primera usando INDEX-MATCH. Aquí tienes las tablas:

Como ves, a la primera tabla le falta la descripción. Vamos a unir ahora el nombre con su descripción usando INDEX-MATCH:

Fácil, ¿verdad? Como se muestra en el vídeo, el primer paso es seleccionar en INDEX el column_range que contiene los datos que queremos recuperar. En este caso, la columna Description (M2:M8). El segundo paso es seleccionar en MATCH el lookup_value, es decir, la celda B2. Por último, añadimos el lookup_column_range (L2:L8) dentro de MATCH y elegimos 0 como match_type porque queremos una coincidencia exacta del lookup_value. El resto es pulsar ENTER y arrastrar la fórmula hacia abajo para finalmente unir el nombre con las descripciones.

INDEX-MATCH también puede usarse para unir tablas que están en hojas distintas, igual que con VLOOKUP. Aquí tienes un ejemplo en vídeo:

Nota: Habrás notado que los rangos de columna que selecciono en los vídeos suelen ir rodeados de signos $. Esto se hace para fijar los rangos y que no cambien cuando arrastras las fórmulas en vertical u horizontal. Mantendré esa notación a lo largo del tutorial.

Hasta aquí, todo se comporta como un VLOOKUP sencillo, pero con un poco más de trabajo. Aun así, como mencionábamos al principio, hay varias ventajas si optas por INDEX-MATCH. Una de las más útiles es la posibilidad de usar referencias de columna dinámicas. Es decir, poder añadir columnas en el rango de la tabla donde haces las búsquedas sin que la búsqueda se rompa.

Si añadimos una nueva columna en nuestro ejemplo y estuviéramos usando VLOOKUP, tendríamos que reescribir la fórmula de la tabla de la izquierda para que apunte a la descripción, que pasaría a ser la columna 3. Sin embargo, si usas INDEX-MATCH, que cuenta con referencias de columna dinámicas, esto no hace falta. Esta comparación puede verse en el vídeo siguiente:

En el ejemplo anterior, la columna que hacía la búsqueda con INDEX-MATCH se ajustó automáticamente cuando añadimos la nueva columna con los tipos de naves espaciales, mientras que la columna con VLOOKUP no lo hizo. Esta ventaja de INDEX-MATCH sobre VLOOKUP brilla especialmente en tareas con búsquedas en tablas grandes de Excel, como un informe de ventas. Imagina que tienes varios cuadros de mando en Excel que hacen referencia a una única tabla con todos los datos. Si usas VLOOKUP y un día necesitas insertar una columna en medio para añadir otra variable, tendrías que cambiar las llamadas a VLOOKUP en todos los dashboards que dependen de esa tabla. En cambio, si construiste los dashboards usando INDEX-MATCH como método de búsqueda, no tendrás ese problema.

INDEX-MATCH avanzado: coincidencia con distinción de mayúsculas y minúsculas

Las referencias de columna dinámicas son muy útiles, pero no son la única ventaja de INDEX-MATCH. Por ejemplo, es posible ampliar la sintaxis básica para hacer búsquedas que distingan mayúsculas y minúsculas. En concreto, vamos a añadir la función EXACT dentro de MATCH para lograrlo. La sintaxis detallada es la siguiente:

{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}

• data_range: El rango de la tabla que nos interesa y que incluye la columna de búsqueda y la columna de la que queremos recuperar el dato.
• lookup_value: El valor que quieres buscar (la clave).
• lookup_column_range: El rango de la columna que contiene las claves que quieres hacer coincidir.
• match_type: 0 o 1. Con 0 indicas coincidencia exacta y con 1 coincidencia aproximada. Es análogo al indicador FALSO/VERDADERO de VLOOKUP.
• desired_column_number: Igual que el índice de columna en VLOOKUP. Es el índice de la columna que contiene el dato que quieres obtener.

Las búsquedas que distinguen mayúsculas y minúsculas con esta variante de INDEX-MATCH son muy útiles cuando trabajas con claves que incluyen letras y se diferencian por el uso de mayúsculas (por ejemplo, si tienes dos claves diferentes: "00567UUp" y "00567UUP"). Estas situaciones son habituales, por ejemplo, en el popular CRM Salesforce al intentar hacer coincidir Account IDs o IDs de oportunidades de venta. Por ahora no usaremos datos de Salesforce, sino una versión adaptada de nuestras tablas de ejemplo en el siguiente vídeo para ver la fórmula en acción.

Nota: La combinación de funciones anterior va entre llaves. Esto significa que es una fórmula matricial y debes introducirla con Ctrl+Shift+Enter.

Como ves, la sintaxis puede ser larga de escribir, pero los resultados son los esperados. Busca los valores igual que el INDEX-MATCH normal, pero teniendo en cuenta el uso de mayúsculas y minúsculas. La "magia" la hace EXACT, que devuelve una matriz de TRUE y FALSE. Donde aparece TRUE en esa matriz, significa que hay un valor con la misma capitalización exacta. Después, MATCH obtiene la posición de la primera aparición de TRUE.

Ten en cuenta que es crucial pulsar Ctrl+Shift+Enter al terminar de escribir la fórmula, o recibirás un error #N/A difícil de diagnosticar.

INDEX-MATCH avanzado: coincidencia basada en varios criterios

Las búsquedas que distinguen mayúsculas y minúsculas en Excel son muy útiles, pero hay otra función aún mejor: las búsquedas que tienen en cuenta múltiples criterios. Considerar varios criterios es algo común en funciones condicionales de conteo o suma como COUNTIFS o SUMIFS, pero es menos habitual verlo en funciones de búsqueda en Excel. Aun así, puede ser muy ventajoso en ciertas situaciones. Por ejemplo, si tienes un archivo de RR. HH. en Excel y quieres buscar el salario de una persona en base a su nombre Y apellidos. Si solo tienes en cuenta una de las dos columnas, es posible que recuperes el salario de la persona equivocada, ya que puede haber muchas con el mismo nombre o apellido. Sin embargo, es mucho más probable acertar si recuperas el valor basándote en nombre y apellidos a la vez.

Dicho esto, veamos la estructura de INDEX-MATCH teniendo en cuenta múltiples criterios:

{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}

En esencia, la fórmula tiene los mismos componentes que el INDEX-MATCH normal, pero ahora puede considerar $n$ valores de búsqueda con su respectivo número de rangos de búsqueda. Si necesitas refrescar cuáles son esos componentes, desplázate arriba a la sintaxis básica de INDEX-MATCH. La razón por la que usamos un "1" en MATCH es que esta fórmula crea matrices de 1 y 0 que representan las coincidencias de cada criterio. Luego, MATCH obtiene la posición del primer 1 que encuentra.

Igual que en el caso de la búsqueda que distingue mayúsculas, esta fórmula también va entre llaves, lo que significa que debes pulsar Ctrl+Shift+Enter al terminar de escribirla, ya que es una fórmula matricial.

Veamos un ejemplo de cómo funciona esta fórmula en la práctica seleccionando naves espaciales que pertenecen a una raza concreta y que tienen una determinada velocidad máxima de curvatura en el siguiente vídeo:

¡Voilà! Ahora ya tienes las herramientas para hacer búsquedas basadas en múltiples claves. Ten en cuenta que, en este ejemplo sencillo, solo he usado dos: la clave y la velocidad máxima de curvatura; pero, en teoría, puedes seguir añadiendo criterios. Volviendo al ejemplo de RR. HH., podrías hacer la coincidencia por nombre, apellidos y puesto para minimizar la probabilidad de recuperar el salario de la persona equivocada.

Conclusión

¡Enhorabuena! Ya dominas INDEX-MATCH y todo lo que puedes hacer con esta combinación más allá de una simple búsqueda vertical. Si en algún momento te encargas de informes complejos en Excel, es muy probable que estas funciones se conviertan en tus grandes aliadas. Como muchos, en el colegio aprendí VLOOKUP, pero cuando empecé a usar Excel en serio para tareas como las búsquedas con distinción de mayúsculas en los IDs de cuentas de Salesforce, me di cuenta de que INDEX-MATCH era una opción mucho mejor a largo plazo, a pesar de que la fórmula pueda ser más laboriosa y propensa a errores al escribirla. Te animo a probarla la próxima vez que tengas que hacer una búsqueda vertical en tus proyectos con Excel. Quién sabe, quizá te guste tanto como a mí.

Si te apetece aprender aún más, echa un vistazo al curso Data Analysis with Spreadsheets de DataCamp.