Índice coincidente en Excel: Una forma mejor de buscar datos

INDEX MATCH, una combinación de las dos funciones de Excel, INDEX() y MATCH(), es una alternativa a la función VLOOKUP(), que se utiliza habitualmente.

En esta guía, te explicaré cómo funcionan INDEX() y MATCH() por separado y juntos, cómo realizar búsquedas verticales y horizontales, y las mejores prácticas para utilizar INDEX MATCH con ejemplos prácticos.

¿Qué es INDEX MATCH en Excel?

INDEX MATCH es una forma abreviada de referirse al anidamiento de las dos funciones. Es equivalente a la idea de INDEX(MATCH()). Veamos cómo funcionan ambos por separado y juntos.

Función INDEX() en Excel

La función « INDEX() » (buscar en una tabla) de Excel devuelve un valor o una referencia a un valor dentro de un rango o tabla. Puedes utilizarlo de dos maneras: en forma de arreglo y en forma de referencia.

Forma de arreglo

Puedes utilizar la forma de arreglo cuando trabajas con una tabla o un arreglo fijo. En este formato, INDEX() extrae un valor basado en el número de fila y columna que especificas. Su sintaxis es:

INDEX(array, row_num, [column_num])

Entendamos estos argumentos:

• array (obligatorio) es el rango o arreglo de valores con los que estás trabajando.

• row_num (obligatorio) es el número de fila desde el que se debe devolver un valor.

• column_num (opcional) es el número de columna desde la que se debe devolver un valor.

Debemos especificar al menos uno, ya sea row_num o column_num, para que Excel pueda localizar el valor.

Sin embargo, aquí tienes algunas notas rápidas que debes tener en cuenta al trabajar con esta sintaxis:

• Si el arreglo solo tiene una fila o columna, puedes omitir row_num o column_num.

• Si se incluyen tanto row_num como column_num, INDEX devuelve el valor en el punto donde se cruzan.

• Si estableces row_num o column_num en 0, Excel devuelve toda la columna o fila (deberás introducirla como una fórmula de arreglo para ver el resultado).

Formulario de referencia

Puedes utilizar el formulario de referencia cuando trabajes con varios rangos y necesites especificar cuál de ellos deseas consultar.

Su sintaxis es:

INDEX(reference, row_num, [column_num], [area_num])

Entendamos estos argumentos:

• reference (obligatorio) se utiliza para hacer referencia a uno o varios rangos de celdas.

• row_num (obligatorio) es el número de fila en el área seleccionada.

• column_num (opcional) es el número de columna en el área seleccionada.

• area_num (opcional) indica a Excel qué rango utilizar si hay más de uno.

Aquí hay algunos puntos adicionales que debes recordar:

• Si se omite area_num, Excel utiliza el primer rango de forma predeterminada.

• No puedes mezclar rangos de diferentes hojas en este formulario; se producirá un error.

• Si se omiten tanto la fila como la columna, devuelve el área completa especificada.

Función MATCH() en Excel

El MATCH() función te ayuda a encontrar la posición de un valor en una fila o columna. En lugar de devolver el valor en sí, te indica dónde se encuentra en la lista.

Su sintaxis es:

MATCH(lookup_value, lookup_array, [match_type])

Entendamos estos argumentos:

• lookup_value (obligatorio) es el valor que deseas encontrar. Puede ser un número, texto, valor lógico (VERDADERO/FALSO) o una referencia a una celda.

• lookup_array (obligatorio) es el rango de celdas que Excel buscará.

• match_type (opcional) define cómo Excel busca la coincidencia. El valor predeterminado es 1. Aquí, 1 encuentra el valor más grande menor o igual que lookup_value (requiere orden ascendente). 0 encuentra la coincidencia exacta (en cualquier orden). -1 encuentra el valor más pequeño mayor o igual que lookup_value (requiere orden descendente).

La función « MATCH » no distingue entre mayúsculas y minúsculas, por lo que trata las letras en mayúsculas y minúsculas de la misma manera. Si estás trabajando con texto y deseas utilizar caracteres comodín como * (cualquier número de caracteres) o ? (un solo carácter), asegúrate de establecer match_type en 0. Porque es el único modo que admite la búsqueda con comodines.

¿Cómo funcionan juntas las funciones INDEX() y MATCH() en Excel?

Uno de los usos más comunes de MATCH() es combinarlo con la función INDEX(). Mientras que MATCH() encuentra la posición de un valor, INDEX() recupera el valor en esa posición. 

Al anidar MATCH() dentro de INDEX(), obtienes una fórmula dinámica y resistente cuando las columnas o filas cambian con el tiempo. 

Por ejemplo, tengo un conjunto de datos de muestra. Y si quiero averiguar qué hay en la misma fila dela columna B de « » que en la columna A de «Banana», puedo usar « INDEX MATCH » juntos de esta manera:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))

Aquí, MATCH("Banana", A2:A5, 0) encuentra la posición de Banana en la columna A y INDEX(B2:B5, ...) devuelve el valor de la misma fila en la columna B.

ÍNDICE DE PARTIDAS vs. VLOOKUP(): ¿Cuál deberías usar?

INDEX MATCH puede realizar tareas que VLOOKUP() no puede. Sin embargo, la elección entre uno u otro depende del nivel de flexibilidad y rendimiento que necesites en tus hojas de Excel. Comparémoslos.

Flexibilidad de columnas e integridad de los datos

Uno de los mayores inconvenientes de VLOOKUP() es su dependenciaen un número fijo de columnas. Si insertas o mueves columnas, la fórmula puede romperse.

Tomemos este ejemplo:

Si se añade una nueva columna entre columna A y columna B, la tercera columna se desplaza y la fórmula devuelve resultados incorrectos, como puedes ver en la imagen siguiente.

=VLOOKUP("Apple", A2:C5, 2, FALSE)

INDEX MATCH evita este problema trabajando con rangos definidos en lugar de posiciones de columna codificadas de forma rígida:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))

Incluso si se reorganizan las columnas, esta fórmula sigue siendo eficaz, lo que la hace más fiable y fácil de mantener. Aquí, se inserta una columna antes de la columna B; la fórmula se actualiza automáticamente y los resultados siguen siendo correctos. Esta es la magia de un INDEX MATCH.

Dirección de búsqueda y versatilidad

VLOOKUP() solo se puede buscar de izquierda a derecha. Esto significa que la columna de búsqueda siempre debe ser la primera del rango. Si el valor que necesitas se encuentra a la izquierda de la columna de búsqueda, VLOOKUP() no podrá encontrarlo.

Sin embargo, si buscamos el nombre de la fruta para encontrar su color a la derecha, esta fórmula funcionará. Por ejemplo, si queremos encontrar el color de un plátano en nuestra lista, nos da el resultado exacto: 

Con INDEX MATCH, esa limitación no existe. Puedes buscar en cualquier dirección: izquierda, derecha, arriba o abajo. También admite búsquedas horizontales para ofrecerte un mayor control sobre cómo organizas tus datos. 

Apliquemos INDEX MATCH a este ejemplo y veamos cómo permite búsquedas tanto a la izquierda como a la derecha.

Para la búsqueda a la izquierda, puedes utilizar la siguiente fórmula, que te dará el resultado exacto: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))

Para realizar la búsqueda correcta, puedes utilizar la siguiente fórmula, que te dará el resultado adecuado: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))

Rendimiento y eficiencia en Excel

VLOOKUP() escanea toda la tabla para encontrar una coincidencia y recuperar un valor de una sola vez. Pero INDEX MATCH divide la tarea: MATCH() encuentra la fila o columna, y INDEX() recupera el valor. 

Esto lo hace más rápido y eficiente en cuanto a la memoria, especialmente en cuadernos de trabajo complejos o de gran volumen.

Aquí tienes una tabla para que te hagas una idea más clara de qué función es compatible con cada funcionalidad:

Cuándo utilizar INDEX MATCH

Puedes utilizar INDEX MATCH cuando:

• Trabajas con grandes conjuntos de datos en los que la velocidad es importante.
• Tu modelo es dinámico, con columnas que se añaden o reorganizan.
• Debes buscar los valores a la izquierda o en rangos horizontales.

Si tus archivos Excel son pequeños y sencillos, VLOOKUP() puede seguir siendo una buena opción. Sin embargo, para modelado y generación de informes avanzados, INDEX MATCH es una opción más segura.

Cómo usar INDEX MATCH paso a paso

Supongamos que tienes dos columnas: Frutas y ventas. Quieres encontrar el valor de las ventas de «Apple».

Para ello, escribe la fórmula combinada INDEX MATCH y pulsa Intro:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Aquí, MATCH("Apple", A2:A6, 0) encuentra la posición de «Apple» en la listaFruits . Y INDEX(B2:B6, ...) utiliza esa posición para devolver el valor de ventas correspondiente de la columnaVentas de .

Puedes ver que la fórmula ha devuelto 12000, que es el valor de las ventas de Apple. Así que ese es tu resultado.

Cómo evitar errores INDEX MATCH

Aunque « INDEX MATCH » es una buena combinación, puede seguir generando errores si no se configura correctamente. A continuación, te explicamos cómo solucionar los problemas más comunes y evitar errores que podrían dañar tus fórmulas.

Errores #N/A y cómo resolverlos

El error « #N/A » significa que Excel no ha podido encontrar el valor que estás buscando. Algunas causas comunes detrás de esto podrían ser:

• Errores tipográficos, espacios adicionales o texto que no coincide (por ejemplo, «apple» en lugar de «apple»). "Apple")

• No hay ningún valor en el rango de búsqueda.

• Incorrecto: match_type, por ejemplo, 1 en lugar de 0 para una coincidencia exacta.

• Caracteres ocultos (como espacios no separables) en los datos

Hay dos formas de solucionar esto:

Utiliza TRIM() para limpiar los espacios al principio o al final:

=MATCH(TRIM("Apple"), A2:A6, 0)

Añade IFERROR() para mostrar un valor alternativo en lugar de un error:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")

Errores #REF! en fórmulas INDEX MATCH

El error « #REF! » suele indicar una referencia rota, a menudo debido a:

• No coinciden los números de filas de los rangos INDEX() y MATCH().

• Filas o columnas eliminadas utilizadas en tu fórmula

• Copiar y pegar fórmulas sin actualizar las referencias de celda

Pero hay algunas formas sencillas de solucionarlo: 

• Asegúrate de que los rangos INDEX() y MATCH() sean del mismo tamaño:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

• Bloquea rangos con referencias absolutas (como $A$2:$A$6) para mantener las fórmulas estables durante el proceso de copiar y pegar.

• Evita eliminar celdas de las que dependen fórmulas.

Rendimiento lento y conjuntos de datos grandes

Si trabajas con miles de filas, las fórmulas de búsqueda de texto ( INDEX MATCH ) no optimizadas pueden ralentizar Excel.

A continuación te explicamos cómo puedes optimizarlo:

• Limita los rangos de búsqueda en lugar de hacer referencia a columnas completas:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))

• Evita funciones volátiles como OFFSET(), INDIRECT()y NOW() cerca de tus búsquedas.

• Usa tablas de Excel o rangos con nombre para mantener todo organizado y mejorar el rendimiento.

Gestión de errores de desbordamiento en Excel 365

Excel 365 introduce los arreglos dinámicos, que pueden «derramar» los resultados en celdas adyacentes. Si hay algún problema con ese proceso, aparecerá un error #SPILL!.

Algunas causas comunes podrían ser:

• Las celdas adyacentes no están vacías.
• La fórmula devuelve varios valores en un rango de celdas bloqueadas o combinadas.
• Estás utilizando un arreglo dinámico donde solo se necesita un valor.

Pero estos problemas se pueden solucionar. Así es como puedes superarlos:

• Borra las celdas debajo o al lado de la fórmula.
• Descombina las celdas del área de desbordamiento.
• Si solo deseas un valor, fuerza un único resultado con @:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))

Reflexiones finales

Si has estado utilizando VLOOKUP(),, ahora es el momento de cambiar a algo másestable. Puedes empezar con algunas búsquedas sencillas utilizando INDEX MATCH en una copia de tu archivo de trabajo. Prueba a sustituir la nueva fórmula cuando los cambios en las columnas provoquen errores o cuando la estructura de los datos requiera flexibilidad. Una vez que veas lo bien que gestiona esos casos, te resultará difícil volver atrás.