Excel INDEX MATCH: Búsquedas básicas y avanzadas

En Excel, recuperar datos específicos con precisión es una necesidad frecuente. Aunque las funciones INDEX y MATCH tienen sus propias ventajas, combinarlas desbloquea un potente conjunto de herramientas para la búsqueda de datos. Juntas, permiten una amplia variedad de búsquedas, desde las más básicas (horizontales y verticales) hasta funcionalidades avanzadas como búsquedas bidireccionales, sensibles a mayúsculas y minúsculas, y con múltiples criterios. Ofreciendo capacidades superiores a VLOOKUP, la combinación de INDEX y MATCH permite realizar búsquedas de datos mucho más versátiles. En este tutorial, profundizaremos en todas las posibilidades que ofrecen juntas.

• Función INDEX
• Función MATCH
• Combinar INDEX y MATCH

• Búsqueda bidireccional
• Búsqueda a la izquierda
• Búsqueda sensible a mayúsculas y minúsculas
• Coincidencia más cercana
• Búsqueda con múltiples criterios
• Búsqueda en varias columnas
• Búsqueda del primer valor no vacío
• Búsqueda del primer valor numérico
• Búsqueda de asociaciones con valores MÁX o MÍN
• Consejo: Personaliza tus propios mensajes de error #N/A

Cómo usar INDEX y MATCH en Excel

Antes de utilizar las funciones INDEX y MATCH, asegurémonos primero de entender cómo pueden ayudarnos a buscar valores.

Cómo usar la función INDEX en Excel

La función INDEX en Excel devuelve el valor de una ubicación específica dentro de un rango determinado. La sintaxis de la función INDEX es la siguiente:

• array (obligatorio) se refiere al rango del que deseas devolver el valor.
• row_num (obligatorio, a menos que se indique column_num) se refiere al número de fila del array.
• column_num (opcional, pero obligatorio si se omite row_num) se refiere al número de columna del array.

Por ejemplo, para saber la puntuación de Jeff, el sexto estudiante de la lista, puedes utilizar la función INDEX de la siguiente manera:

=INDEX(C2:C11,6)

√ Nota: El rango C2:C11 es donde están listadas las puntuaciones, mientras que el número 6 localiza la nota del sexto estudiante.

Ahora hagamos una pequeña prueba. Para la fórmula =INDEX(A1:C1,2), ¿qué valor devolverá? --- Exacto, devolverá Birth date, el segundo valor de la fila indicada.

Ya sabemos que la función INDEX puede trabajar perfectamente con rangos horizontales o verticales. Pero, ¿qué pasa si necesitamos que devuelva un valor en un rango mayor con varias filas y columnas? En ese caso, debemos indicar tanto el número de fila como el de columna. Por ejemplo, para encontrar la puntuación de Jeff dentro de una tabla en lugar de una sola columna, podemos localizar su nota con un número de fila6 y un número de columna3 en las celdas de A2 a C11 así:

=INDEX(A2:C11,6,3)

Sin embargo, para una base de datos muy grande con múltiples filas y columnas, no resulta práctico aplicar la fórmula indicando manualmente el número exacto de fila y columna. Es aquí donde debemos combinar el uso de la función MATCH.

Cómo usar la función MATCH en Excel

La función MATCH en Excel devuelve un valor numérico, que indica la posición de un elemento específico dentro de un rango dado. La sintaxis de la función MATCH es la siguiente:

• lookup_value (obligatorio) se refiere al valor que deseas buscar en lookup_array.
• lookup_array (obligatorio) se refiere al rango de celdas donde quieres que MATCH realice la búsqueda.
• match_type (opcional): 1, 0 o -1.
  • 1(predeterminado), MATCH encontrará el valor más grande que sea menor o igual que lookup_value. Los valores en lookup_array deben estar en orden ascendente.
  • 0, MATCH encontrará el primer valor que sea exactamente igual a lookup_value. Los valores en lookup_array pueden estar en cualquier orden. (Si el tipo de coincidencia se establece en0, puedes usar caracteres comodín.)
  • -1, MATCH encontrará el valor más pequeño que sea mayor o igual que lookup_value. Los valores en lookup_array deben estar en orden descendente.

Por ejemplo, para saber la posición de Vera en la lista de nombres, puedes usar la fórmula MATCH así:

=MATCH("Vera",A2:A11,0)

√ Nota: El resultado “4” indica que el nombre “Vera” está en la cuarta posición de la lista.

Ahora que conocemos los usos básicos de las funciones INDEX y MATCH en Excel, pongámonos manos a la obra y combinémoslas.

Cómo combinar INDEX y MATCH en Excel

Consulta el siguiente ejemplo para ver cómo podemos combinar las funciones INDEX y MATCH:

Para encontrar la puntuación de Evelyn, sabiendo que las notas están en la tercera columna, podemos usar la función MATCH para determinar automáticamente la posición de la fila sin necesidad de contar manualmente. Después, empleamos la función INDEX para recuperar el valor en la intersección de la fila identificada y la tercera columna:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Como la fórmula puede parecer un poco compleja, vamos a analizar cada parte.

La fórmula INDEX contiene tres argumentos:

• row_num: MATCH("Evelyn",A2:A11,0)proporciona a INDEX la posición de la fila del valor "Evelyn" en el rango A2:A11, que es5.
• column_num:3 indica la 3ra columna para que INDEX localice la puntuación dentro del array.
• array: A2:C11 indica a INDEX que devuelva el valor coincidente en la intersección de la fila y columna especificadas, dentro del rango de A2 a C11. Finalmente, obtenemos el resultado90.

En la fórmula anterior, utilizamos un valor fijo, "Evelyn". Sin embargo, en la práctica, los valores fijos no son prácticos, ya que habría que modificarlos cada vez que busquemos datos diferentes, como la nota de otro estudiante. En estos casos, podemos utilizar referencias de celda para crear fórmulas dinámicas. Por ejemplo, en este caso, cambiaré "Evelyn" por F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Ejemplos de fórmulas con INDEX y MATCH

En esta sección, veremos diferentes situaciones en las que usar las funciones INDEX y MATCH para cubrir distintas necesidades.

INDEX y MATCH para realizar una búsqueda bidireccional

En el ejemplo anterior, conocíamos el número de columna y utilizamos una fórmula MATCH para encontrar el número de fila. Pero, ¿qué ocurre si tampoco sabemos el número de columna?

En estos casos, podemos realizar una búsqueda bidireccional, también conocida como búsqueda matricial, utilizando dos funciones MATCH: una para encontrar el número de fila y otra para determinar el número de columna. Por ejemplo, para saber la puntuación de Evelyn, debemos usar la siguiente fórmula:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX y MATCH para realizar una búsqueda a la izquierda

Ahora, consideremos un escenario en el que necesitas saber la clase de Evelyn. Puede que hayas notado que la columna de clase está situada a la izquierda de la columna de nombres, una situación que supera las capacidades de otra potente función de búsqueda de Excel, VLOOKUP.

De hecho, la capacidad de realizar búsquedas hacia la izquierda es una de las ventajas por las que la combinación de INDEX y MATCH supera a VLOOKUP.

Para encontrar la clase de Evelyn, utiliza la siguiente fórmula para buscar a Evelyn en B2:B11 y recuperar el valor correspondiente de A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX y MATCH para realizar una búsqueda sensible a mayúsculas y minúsculas

La función MATCH, por defecto, no distingue entre mayúsculas y minúsculas. Sin embargo, si necesitas que tu fórmula diferencie entre caracteres en mayúsculas y minúsculas, puedes mejorarla incorporando la función EXACT. Al combinar MATCH con EXACT en una fórmula INDEX, puedes realizar búsquedas sensibles a mayúsculas y minúsculas, como se muestra a continuación:

• array se refiere al rango del que deseas devolver el valor.
• lookup_value se refiere al valor a buscar, teniendo en cuenta las mayúsculas y minúsculas, en lookup_array.
• lookup_array se refiere al rango de celdas donde quieres que MATCH compare con lookup_value.

Por ejemplo, para saber la nota de examen de JIMMY, utiliza la siguiente fórmula:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Nota: Esta es una fórmula de matriz que debes introducir con Ctrl + Shift + Enter, excepto en Excel365, Excel2021 y versiones más recientes.

INDEX y MATCH para encontrar la coincidencia más cercana

En Excel, es posible que te encuentres con situaciones en las que necesitas encontrar la coincidencia más próxima a un valor específico dentro de un conjunto de datos. En estos casos, la combinación de las funciones INDEX y MATCH, junto con ABS y MIN, puede ser de gran ayuda.

• array se refiere al rango del que deseas devolver el valor.
• lookup_array se refiere al rango de valores donde deseas encontrar la coincidencia más cercana a lookup_value.
• lookup_value se refiere al valor del que deseas encontrar la coincidencia más cercana.

Por ejemplo, para saber quién tiene la puntuación más cercana a85, utiliza la siguiente fórmula para buscar la nota más próxima a85 en C2:C11 y recuperar el valor correspondiente de A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Nota: Esta es una fórmula de matriz que debes introducir con Ctrl + Shift + Enter, excepto en Excel365, Excel2021 y versiones más recientes.

INDEX y MATCH para realizar una búsqueda con múltiples criterios

Para encontrar un valor que cumpla varias condiciones, y que requiera buscar en dos o más columnas, utiliza la siguiente fórmula. Esta fórmula te permite realizar una búsqueda con múltiples criterios especificando distintas condiciones en diferentes columnas, ayudándote a encontrar el valor deseado que cumpla todos los requisitos.

√ Nota: Esta es una fórmula de matriz que debes introducir con Ctrl + Shift + Enter. Aparecerán unas llaves en la barra de fórmulas.

• array se refiere al rango del que deseas devolver el valor.
• (lookup_value=lookup_array) representa una condición individual. Esta condición comprueba si un lookup_value determinado coincide con los valores en lookup_array.

Por ejemplo, para encontrar la nota de Coco de la Clase A, cuya fecha de nacimiento es7/2/2008, puedes usar la siguiente fórmula:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX y MATCH para realizar una búsqueda en varias columnas

Imagina un escenario en el que trabajas con varias columnas de datos. La primera columna actúa como clave para clasificar los datos de las demás columnas. Para determinar la categoría o clasificación de una entrada específica, tendrás que buscar en las columnas de datos y asociarla con la clave correspondiente en la columna de referencia.

Por ejemplo, en la siguiente tabla, ¿cómo podemos emparejar al estudiante Shawn con su clase correspondiente usando INDEX y MATCH? Puedes hacerlo con una fórmula, pero es bastante extensa y difícil de comprender, y mucho más de recordar y escribir.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Aquí es donde la función Índice y coincidencia de múltiples columnas de Kutools para Excel resulta muy útil. Simplifica el proceso, permitiendo emparejar rápidamente entradas específicas con sus categorías correspondientes. Para aprovechar esta potente herramienta y emparejar fácilmente a Shawn con su clase, simplemente descarga e instala el complemento Kutools para Excel y sigue estos pasos:

1. Selecciona la celda de destino donde quieres mostrar la clase coincidente.
2. En la pestaña Kutools, haz clic en Asistente de fórmulas > Búsqueda y referencia > Índice y coincidencia de múltiples columnas.
3. 
3. En el cuadro de diálogo emergente, haz lo siguiente:
   1. Haz clic en el primer botón junto a Columna de búsqueda para seleccionar la columna que contiene la información clave que deseas devolver, es decir, los nombres de las clases. (Solo puedes seleccionar una columna aquí.)
   2. Haz clic en el segundo botón junto a Rango de tabla para seleccionar las celdas que coincidirán con los valores en la Columna de búsqueda, es decir, los nombres de los estudiantes.
   3. Haz clic en el tercer botón junto a Valor de búsqueda para seleccionar la celda que contiene el nombre del estudiante que deseas emparejar con su clase, en este caso, Shawn.
   4. Haz clic en OK.
   5. 

Resultado

Kutools ha generado automáticamente la fórmula y verás el nombre de la clase de Shawn mostrado inmediatamente en la celda de destino.

INDEX y MATCH para buscar el primer valor no vacío

Para recuperar el primer valor no vacío, ignorando los errores, de una columna o fila, puedes utilizar una fórmula basada en las funciones INDEX y MATCH. Sin embargo, si no quieres ignorar los errores de tu rango, añade la función ISBLANK.

• Obtener el primer valor no vacío en una columna o fila ignorando errores:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Obtener el primer valor no vacío en una columna o fila incluyendo errores:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX y MATCH para buscar el primer valor numérico

Para recuperar el primer valor numérico de una columna o fila, utiliza la fórmula basada en las funciones INDEX, MATCH e ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX y MATCH para buscar asociaciones con valores MÁX o MÍN

Si necesitas recuperar un valor asociado al valor máximo o mínimo dentro de un rango, puedes usar las funciones MAX o MIN junto con INDEX y MATCH.

• INDEX y MATCH para recuperar un valor asociado al valor máximo:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX y MATCH para recuperar un valor asociado al valor mínimo:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• Hay dos argumentos en las fórmulas anteriores:
• • array se refiere al rango del que deseas devolver la información relacionada.
  • lookup_array representa el conjunto de valores que se examinarán o buscarán según criterios específicos, es decir, valores máximos o mínimos.

Por ejemplo, si quieres saber quién tiene la puntuación más alta, utiliza la siguiente fórmula:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Consejo: Personaliza tus propios mensajes de error #N/A

Al trabajar con las funciones INDEX y MATCH de Excel, es posible que te encuentres con el error #N/A cuando no hay ningún resultado coincidente. Por ejemplo, en la tabla siguiente, al intentar encontrar la nota de una estudiante llamada Samantha, aparece el error #N/A porque no está presente en el conjunto de datos.

Para que tus hojas de cálculo sean más intuitivas, puedes personalizar este mensaje de error envolviendo tu fórmula INDEX MATCH en la función IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Arriba tienes todo el contenido relevante relacionado con las funciones INDEX y MATCH en Excel. Espero que este tutorial te haya resultado útil. Si quieres descubrir más trucos y consejos de Excel, haz clic aquí para acceder a nuestra amplia colección de miles de tutoriales.