ÍNDICE y COINCIDIR en Excel: búsquedas básicas y avanzadas

En Excel, recuperar datos específicos con precisión es una necesidad habitual. Aunque las funciones ÍNDICE y COINCIDIR ofrecen ventajas individuales, al combinarlas se desbloquea un potente conjunto de herramientas para la búsqueda de datos. Juntas permiten una amplia gama de capacidades: desde búsquedas horizontales y verticales básicas hasta funcionalidades avanzadas, como búsquedas bidireccionales, sensibles a mayúsculas y minúsculas, o basadas en múltiples criterios. Al superar las limitaciones de BUSCARV, la combinación de ÍNDICE y COINCIDIR brinda mayor flexibilidad y opciones para localizar datos con exactitud. En este tutorial, exploraremos en profundidad todo lo que pueden lograr juntas.

• Función ÍNDICE
• Función COINCIDIR
• Combinar ÍNDICE y COINCIDIR

• Búsqueda bidireccional
• Búsqueda hacia la izquierda
• Búsqueda sensible a mayúsculas y minúsculas
• Coincidencia más cercana
• Búsqueda con varios criterios
• Búsqueda en varias columnas
• Búsqueda del primer valor no vacío
• Buscar el primer valor numérico
• Búsqueda de asociaciones MAX o MIN
• Consejo: Personalice sus propios mensajes de error #N/A

Cómo usar ÍNDICE y COINCIDIR en Excel

Antes de usar las funciones ÍNDICE y COINCIDIR, asegurémonos de entender cómo pueden ayudarnos a buscar valores.

Cómo usar la función ÍNDICE en Excel

La función ÍNDICE en Excel devuelve el valor situado en una ubicación determinada dentro de un rango específico. Su sintaxis es la siguiente:

• matriz (obligatorio) hace referencia al rango del que desea obtener el valor.
• núm_fila(obligatorio, salvo que)núm_columna esté presente) hace referencia al número de fila de la matriz.
• núm_columna(opcional, pero obligatorio si se omite)núm_fila) hace referencia al número de columna de la matriz.

Por ejemplo, para conocer la puntuación de Jeff, el 6º estudiante de la lista, puede utilizar la función ÍNDICE de la siguiente manera:

=INDEX(C2:C11,6)

√ Nota: El rango C2:C11es donde se enumeran las puntuaciones, mientras que el número 6encuentra la puntuación del examen del 6º estudiante.

Hagamos ahora una pequeña prueba. Para la fórmula =ÍNDICE(A1:C1;2), ¿qué valor devolverá? —¡Sí! Devolverá Fecha de nacimiento, el 2º valor de la fila indicada.

Ahora ya sabemos que la función ÍNDICE funciona perfectamente con rangos horizontales o verticales. Pero, ¿y si necesitamos que devuelva un valor en un rango más amplio con varias filas y columnas? En ese caso, debemos especificar tanto un número de fila como un número de columna. Por ejemplo, para averiguar la puntuación de Jeff dentro del rango de la tabla —en lugar de limitarnos a una sola columna—, podemos localizar su puntuación indicando un número de fila 6 y un número de columna 3 en el rango de celdas desde A2 hasta C11 de la siguiente forma:

=INDEX(A2:C11,6,3)

Sin embargo, en una base de datos muy grande con múltiples filas y columnas, aplicar la fórmula con un número exacto de fila y columna ciertamente no resulta práctico. Es aquí cuando debemos combinarla con la función COINCIDIR.

Cómo usar la función COINCIDIR en Excel

La función COINCIDIR en Excel devuelve un valor numérico: la posición de un elemento específico dentro del rango indicado. Su sintaxis es la siguiente:

• valor_buscado (obligatorio) hace referencia al valor que se debe buscar en la matriz_buscada.
• matriz_buscada (obligatorio) hace referencia al rango de celdas en el que desea que COINCIDIR realice la búsqueda.
• tipo_de_coincidencia(opcional):1,0o -1.
  • 1 (valor predeterminado): COINCIDIR encontrará el valor más grande que sea menor o igual que el valor_buscado. Los valores de la matriz_buscada deben estar en orden ascendente.
  • 0: COINCIDIR encontrará el primer valor que coincida exactamente con el valor_buscado. Los valores del matriz_buscada pueden estar en cualquier orden. (Cuando el tipo de coincidencia se establece en 0, puedes usar caracteres comodín.)
  • -1, COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor_buscado. Los valores de la matriz_buscada deben estar en orden descendente.

Por ejemplo, para conocer la posición de Vera en la Lista de nombres, puede utilizar la Distinguir fórmulas de la siguiente manera:

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

√ Nota: El resultado «4» indica que el nombre «Vera» aparece en la cuarta posición de la lista.

Ahora que ya conocemos los usos básicos de las funciones ÍNDICE y COINCIDIR en Excel, ¡manos a la obra y combinémoslas!

Cómo combinar ÍNDICE y COINCIDIR en Excel

Consulte el ejemplo siguiente para entender cómo podemos combinar las funciones ÍNDICE y COINCIDIR:

Para encontrar la puntuación de Evelyn, sabiendo que las puntuaciones del examen están en la 3ª columna, podemos usar la función COINCIDIR para determinar automáticamente la posición de la fila sin necesidad de contarla manualmente. A continuación, empleamos la función ÍNDICE para obtener el valor situado en la intersección de la fila identificada y la 3ª columna:

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

Como la fórmula puede parecer un tanto complicada, repasemos cada una de sus partes.

La fórmula de ÍNDICEcontiene tres argumentos:

• núm_fila:COINCIDIR(«Evelyn»,A2:A11,0)proporciona a ÍNDICE la posición de fila del valor "Evelyn" en el rango A2:A11, que es5.
• núm_columna: 3 especifica la 3ª columna para que ÍNDICE localice la puntuación dentro de la matriz.
• matriz: A2:C11 indica a ÍNDICE que devuelva el valor coincidente en la intersección de la fila y la columna especificadas, dentro del rango que va desde A2 hasta C11. Finalmente, obtenemos el resultado 90.

En la fórmula anterior, utilizamos un valor escrito directamente en la fórmula: «Evelyn». Sin embargo, en la práctica, los valores introducidos de forma estática no resultan prácticos, ya que habría que modificarlos cada vez que quisiéramos buscar otros datos, como la puntuación de otro estudiante. En estos casos, podemos usar referencias de celda para crear fórmulas dinámicas. Por ejemplo, en este caso, sustituiré «Evelyn» por F2:

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

Ejemplos de ÍNDICE y Distinguir fórmulas

En esta sección, exploraremos diversos escenarios en los que las funciones ÍNDICE y COINCIDIR se pueden utilizar para satisfacer distintas necesidades.

ÍNDICE y COINCIDIR para realizar una búsqueda bidireccional

En el ejemplo anterior, conocíamos el número de columna y utilizábamos una fórmula DISTINGUIR para hallar el número de fila. Pero, ¿y si tampoco estamos seguros del número de columna?

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

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

ÍNDICE y COINCIDIR para realizar una búsqueda hacia la izquierda

Ahora imaginemos un escenario en el que necesite determinar la clase de Evelyn. Habrá notado que la columna de clase se encuentra 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: BUSCARV.

De hecho, la capacidad de buscar hacia la izquierda es una de las ventajas clave que ofrece la combinación de ÍNDICE y COINCIDIR frente a BUSCARV.

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

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

ÍNDICE y COINCIDIR para realizar una búsqueda sensible a mayúsculas/minúsculas

La función COINCIDIR no distingue entre mayúsculas y minúsculas por defecto. Sin embargo, si necesita que su fórmula diferencie entre caracteres en mayúsculas y minúsculas, puede mejorarlo incorporando la función EXACTO. Al combinar COINCIDIR con EXACTO en una fórmula de ÍNDICE, podrá realizar búsquedas sensibles a mayúsculas y minúsculas de forma eficaz, como se muestra a continuación:

• matriz hace referencia al rango del que desea devolver el valor.
• valor_buscado hace referencia al valor que se debe buscar, respetando mayúsculas y minúsculas, en la matriz_buscada.
• matriz_buscada hace referencia al rango de celdas en el que desea que COINCIDIR compare con el valor_buscado.

Por ejemplo, para conocer la puntuación del examen de JIMMY, utilice la siguiente fórmula:

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

√ Nota: Esta es una fórmula matricial que debe introducirse pulsando Ctrl+Mayús+Intro, salvo en Excel 365, Excel 2021 y versiones posteriores.

ÍNDICE y COINCIDIR para encontrar la coincidencia más cercana

En Excel, puede encontrarse con situaciones en las que necesite hallar el valor más próximo o cercano a un dato específico dentro de un conjunto de datos. En tales escenarios, la combinación de las funciones ÍNDICE y COINCIDIR, junto con las funciones ABS y MIN, puede resultar extremadamente útil.

• matriz hace referencia al rango del que desea devolver el valor.
• matriz_buscada hace referencia al rango de valores en el que desea encontrar la coincidencia más cercana al valor_buscado.
• valor_buscado hace referencia al valor del que desea encontrar su coincidencia más cercana.

Por ejemplo, para averiguar de quién es la puntuación más cercana a 85, utilice la siguiente fórmula para buscar la puntuación más cercana a 85 en C2:C11 y obtener 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 matricial que debe introducirse pulsando Ctrl+Mayús+Intro, salvo en Excel 365, Excel 2021 y versiones posteriores.

ÍNDICE y COINCIDIR para realizar una búsqueda con múltiples criterios

Para encontrar un valor que cumpla varias condiciones, lo que requiere buscar en dos o más columnas, utilice la siguiente fórmula. Esta fórmula le permite realizar una búsqueda con múltiples criterios especificando distintas condiciones en diferentes columnas, ayudándole a hallar el valor deseado que cumpla todos los criterios establecidos.

√ Nota: Esta es una fórmula matricial que debe introducirse pulsando Ctrl + Mayús + Intro. Aparecerá entonces un par de llaves en la barra de fórmulas.

• matriz hace referencia al rango del que desea devolver el valor.
• (valor_buscado=matriz_buscada) representa una condición única. Esta condición comprueba si un valor_buscado determinado coincide con los valores de la matriz_buscada.

Por ejemplo, para encontrar la puntuación de Coco de la Clase A, cuya fecha de nacimiento es 7/2/2008, puede utilizar la siguiente fórmula:

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

Usar ÍNDICE y COINCIDIR para realizar una búsqueda en varias columnas

Imagine un escenario en el que trabaja con múltiples 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, deberá realizar una búsqueda en las columnas de datos y asociarla con la clave correspondiente en la columna de referencia.

Por ejemplo, en la tabla siguiente, ¿cómo podemos asociar al estudiante Shawn con su clase correspondiente mediante las funciones ÍNDICE y COINCIDIR? Pues bien, puede lograrlo con una fórmula, pero esta es bastante extensa y puede resultar difícil de comprender, por no hablar de recordarla y escribirla.

=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 entra en juego la potente función Índice y coincidencia de múltiples columnas de Kutools para Excel. Simplifica el proceso, haciendo que sea rápido y sencillo asociar entradas específicas con sus categorías correspondientes. Para desbloquear esta herramienta y asociar sin esfuerzo a Shawn con su clase, simplemente descargue e instale el complemento Kutools para Excel y siga estos pasos:

1. Seleccione la celda de destino en la que desea mostrar la clase coincidente.
2. En la pestaña Kutools, haga clic en Asistente de fórmulas > Buscar y referencia > Índice y coincidencia de múltiples columnas.
3. 
3. En el cuadro de diálogo emergente, proceda de la siguiente manera:
   1. Haga clic en el primer botón junto a Lookup_col para seleccionar la columna que contiene la información clave que desea devolver, es decir, los nombres de las clases. (Solo puede seleccionar una única columna aquí.)
   2. Haga clic en el segundo botón junto a Table_rng para seleccionar las celdas cuyos valores deben coincidir con los de la columna seleccionada Lookup_col, es decir, los nombres de los alumnos.
   3. Haga clic en el tercer botón junto a Lookup_value para seleccionar la celda que contiene el nombre del alumno cuya clase desea buscar; en este caso, Shawn.
   4. Haga clic en Aceptar.
   5. 

Resultado

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

Usar ÍNDICE y COINCIDIR para buscar el primer valor no vacío

Para recuperar el primer valor no vacío, ignorando errores, de una columna o una fila, puede utilizar una fórmula basada en las funciones ÍNDICE y COINCIDIR. Sin embargo, si no desea ignorar los errores del rango, añada la función ESBLANCO.

• 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))

  

Usar ÍNDICE y COINCIDIR para buscar el primer valor numérico

Para recuperar el primer valor numérico de una columna o una fila, utilice una fórmula basada en las funciones ÍNDICE, COINCIDIR y ESNÚMERO.

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

Usar ÍNDICE y COINCIDIR para buscar asociaciones con el valor MÁXIMO o MÍNIMO

Si necesita recuperar un valor asociado al máximo o Valor mínimo dentro de un rango, puede utilizar la función MÁXIMO o MÍNIMO junto con las funciones ÍNDICE y COINCIDIR.

• ÍNDICE y COINCIDIR para recuperar un valor asociado al Valor máximo:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• ÍNDICE y COINCIDIR 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:
• • matriz hace referencia al rango del que desea obtener la información relacionada.
  • matriz_búsqueda representa el conjunto de valores que se examinan o buscan según criterios específicos, es decir, el valor máximo o mínimo.

Por ejemplo, si desea determinar quién tiene la puntuación más alta, utilice la siguiente fórmula:

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

Consejo: Personalice sus propios mensajes de error #N/A

Al trabajar con las funciones ÍNDICE y COINCIDIR de Excel, puede encontrarse con el error #N/A cuando no hay ningún resultado coincidente. Por ejemplo, en la tabla siguiente, al intentar encontrar la puntuación de una estudiante llamada Samantha, aparece un error #N/A porque no está presente en el conjunto de datos.

Para hacer que sus hojas de cálculo sean más amigables, puede personalizar este mensaje de error envolviendo su fórmula ÍNDICE Distinguir fórmulas en la función SISNA:

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

Lo anterior es todo el contenido relevante sobre las funciones ÍNDICE y COINCIDIR en Excel. ¡Esperamos que este tutorial le resulte útil! Si desea descubrir más consejos y trucos de Excel, haga clic aquí para acceder a nuestra amplia colección con miles de tutoriales.