En Excel, recuperar datos específicos con precisión suele ser una necesidad frecuente. Si bien las funciones ÍNDICE y COINCIDIR tienen cada una sus propias fortalezas, al combinarlas se desbloquea un poderoso conjunto de herramientas para la búsqueda de datos. Juntos, facilitan una variedad de capacidades de búsqueda, desde búsquedas horizontales y verticales básicas hasta funcionalidades más avanzadas como búsquedas bidireccionales, que distinguen entre mayúsculas y minúsculas y con criterios múltiples. Al ofrecer capacidades mejoradas en comparación con BUSCARV, la combinación de ÍNDICE y COINCIDIR permite una gama más amplia de opciones de búsqueda de datos. En este tutorial, profundicemos en la profundidad de las posibilidades que pueden lograr juntos.

• Función ÍNDICE
• Función MATCH
• Combina ÍNDICE y COINCIDIR

• Búsqueda bidireccional
• búsqueda izquierda
• Búsqueda sensible a mayúsculas y minúsculas
• Partido más cercano
• Búsqueda con múltiples criterios
• Búsqueda en varias columnas
• Búsqueda del primer valor que no esté en blanco
• Búsqueda del 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 INDICE y COINCIDIR, asegurémonos de saber cómo pueden ayudarnos INDICE y COINCIDIR a buscar valores primero.

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

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

• matriz (obligatorio) se refiere al rango desde el que desea devolver el valor.
• row_num (obligatorio, a menos que column_num está presente) se refiere al número de fila de la matriz.
• column_num (opcional, pero obligatorio si row_num se omite) se refiere al número de columna de la matriz.

Por ejemplo, para saber la partitura de jeff, la 6º estudiante de la lista, puede utilizar la función ÍNDICE de esta manera:

=INDEX(C2:C11,6)

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

Aquí hagamos una pequeña prueba. Para la formula =ÍNDICE(A1:C1,2), ¿qué valor devolverá? --- Sí, volverá Fecha de nacimiento, la 2Segundo valor en la fila dada.

Ahora debemos saber que la función INDICE puede funcionar perfectamente con rangos horizontales o verticales. Pero, ¿qué pasa si necesitamos que devuelva un valor en un rango mayor con varias filas y columnas? Bueno, en este caso, deberíamos aplicar tanto un número de fila como un número de columna. Por ejemplo, para averiguar puntuación de jeff dentro del rango de la tabla en lugar de una sola columna, podemos ubicar su puntuación con un número de fila de 6 y número de columna de 3 existentes celdas desde A2 hasta C11 Me gusta esto:

=INDEX(A2:C11,6,3)

Sin embargo, para una base de datos realmente grande con múltiples filas y columnas, ciertamente no es conveniente para nosotros aplicar la fórmula con un número de fila y de columna exactos. Y esto es cuando debemos combinar el uso de la función COINCIDIR.

¡Asombroso! ¡Usando pestañas eficientes en Excel como Chrome, Edge, Firefox y Safari!
¡Ahorre el 50% de su tiempo y reduzca miles de clics del mouse todos los días!

Cómo utilizar la función COINCIDIR en Excel

La función COINCIDIR en Excel devuelve un valor numérico, la ubicación de un elemento específico en el rango dado. La sintaxis de la función COINCIDIR es la siguiente:

• valor de búsqueda (obligatorio) se refiere al valor que debe coincidir en el lookup_array.
• lookup_array (obligatorio) se refiere al rango de celdas donde desea que coincida la búsqueda.
• tipo de concordancia (Opcional): 1, 0 or -1.
  • 1 (predeterminado), COINCIDIR encontrará el valor más grande que sea menor o igual al valor de búsqueda. Los valores en el lookup_array deben colocarse en orden ascendente.
  • 0, COINCIDIR encontrará el primer valor que sea exactamente igual al valor de búsqueda. Los valores en el lookup_array puede estar en cualquier orden. (Para los casos en que el tipo de concordancia se establece en 0, puede utilizar caracteres comodín).
  • -1, COINCIDIR encontrará el valor más pequeño que sea mayor o igual que el valor de búsqueda. Los valores en el lookup_array deben colocarse en orden descendente.

Por ejemplo, para saber la posición de Vera en la lista de nombres, puede usar la fórmula COINCIDIR como esta:

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

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

Ahora que conocemos los usos básicos de las funciones INDICE y COINCIDIR en Excel, arremangámonos y prepárese para combinar las dos funciones.

Cómo combinar ÍNDICE y COINCIDIR en Excel

Consulte el ejemplo a continuación para descubrir cómo podemos combinar las funciones INDICE y COINCIDIR:

Encontrar La puntuación de Evelyn., sabiendo que las puntuaciones de los exámenes están en el 3tercera columna, podemos Utilice la función COINCIDIR para determinar automáticamente la posición de la fila. sin necesidad de contarlo manualmente. Posteriormente, podemos emplear la función ÍNDICE 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)

Bueno, dado que la fórmula puede parecer un poco complicada, repasemos cada parte.

La ÍNDICE fórmula contiene tres argumentos:

• row_num: COINCIDIR("Evelyn",A2:A11,0) proporciona al ÍNDICE la posición de la fila del valor "Evelyn" en el rango A2: A11, cual es 5.
• column_num: 3 especifica el 3Tercera columna para ÍNDICE para ubicar 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 columna especificadas, dentro del rango que abarca desde A2 a C11. Finalmente obtenemos el resultado. 90.

En la fórmula anterior, empleamos un valor codificado, "Evelyn". Sin embargo, en la práctica, los valores codificados no son prácticos, ya que requerirían modificaciones cada vez que buscamos datos diferentes, como la puntuación de otro estudiante. En tales escenarios, podemos utilizar referencias de celda para crear fórmulas dinámicas. Por ejemplo, en este caso, haré cambiar "Evelyn" a F2:

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

Ejemplos de fórmulas ÍNDICE y COINCIDIR

En esta parte, hablaremos sobre diferentes circunstancias para usar las funciones INDICE y MATCH para satisfacer diferentes necesidades.

ÍNDICE y COINCIDIR para aplicar una búsqueda bidireccional

En el ejemplo anterior, conocíamos el número de columna y utilizamos una fórmula COINCIDIR para encontrar el número de fila. Pero, ¿qué pasa si tampoco estamos seguros del número de columna?

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

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

ÍNDICE y COINCIDIR para aplicar una búsqueda a la izquierda

Ahora, consideremos un escenario en el que necesitas determinar la clase de Evelyn. Es posible que haya notado que la columna de clase está ubicada a la izquierda de la columna de nombre, una situación que excede las capacidades de otra poderosa función de búsqueda de Excel, BUSCARV.

De hecho, la capacidad de realizar búsquedas en el lado izquierdo es uno de los aspectos en los que la combinación de ÍNDICE y COINCIDIR eclipsa a BUSCARV.

Encontrar La clase de evelyn, emplee 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))

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

Las funciones MATCH no distinguen entre mayúsculas y minúsculas. Sin embargo, cuando necesite que su fórmula diferencie entre caracteres en mayúsculas y minúsculas, puede mejorarla incorporando el EXACT función. Al combinar la función COINCIDIR con EXACTO en una fórmula ÍNDICE, puede realizar de manera efectiva una búsqueda que distinga entre mayúsculas y minúsculas, como se muestra a continuación:

• matriz se refiere al rango desde donde desea devolver el valor.
• valor de búsqueda se refiere al valor a igualar, considerando el caso de caracteres, en el lookup_array.
• lookup_array se refiere al rango de celdas con las que desea comparar MATCH valor de búsqueda.

Por ejemplo, para saber Puntaje 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 de matriz que requiere que ingrese con Ctrl + Shift + Participar, excepto en Excel 365 y Excel 2021.

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

En Excel, puede encontrar situaciones en las que necesite encontrar la coincidencia más cercana o más cercana a un valor específico dentro de un conjunto de datos. En tales escenarios, usar una combinación de funciones INDEX y MATCH, junto con las funciones ABS y MIN, puede resultar increíblemente útil.

• matriz se refiere al rango desde donde desea devolver el valor.
• lookup_array se refiere al rango de valores donde desea encontrar la coincidencia más cercana valor de búsqueda.
• valor de búsqueda se refiere al valor para encontrar su coincidencia más cercana.

Por ejemplo, para averiguar cuyo puntaje es más cercano a 85, utilice la siguiente fórmula para busque la puntuación más cercana a 85 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 requiere que ingrese con Ctrl + Shift + Participar, excepto en Excel 365 y Excel 2021.

¡Asombroso! ¡Usando pestañas eficientes en Excel como Chrome, Edge, Firefox y Safari!
¡Ahorre el 50% de su tiempo y reduzca miles de clics del mouse todos los días!

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

Para encontrar un valor que cumpla varias condiciones y requiera buscar en dos o más columnas, emplee la siguiente fórmula. La fórmula le permite realizar una búsqueda de criterios múltiples especificando varias condiciones en diferentes columnas, lo que le ayuda a encontrar el valor deseado que cumpla con todos los criterios especificados.

√ Nota: Esta es una fórmula de matriz que requiere que ingrese con Ctrl + Shift + Participar. A continuación, aparecerán un par de llaves en la barra de fórmulas.

• matriz se refiere al rango desde donde desea devolver el valor.
• (valor_de_buscada=matriz_de_buscada) representa una sola condición. Esta condición verifica si un particular valor de búsqueda coincide con los valores de la lookup_array.

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

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

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

Imagine un escenario en el que trabaja con varias columnas de datos. La primera columna actúa como clave para clasificar los datos de las otras 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 relevante en la columna de referencia.

Por ejemplo, en la siguiente tabla, ¿cómo podemos relacionar al estudiante Shawn con su clase correspondiente usando INDEX y MATCH? Bueno, puedes lograrlo con una fórmula, pero la fórmula es bastante extensa y puede ser difícil de comprender, y mucho menos 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)), "")

Ahí es donde Kutools for Excel's Indexar y hacer coincidir en varias columnas La característica es útil. Simplifica el proceso, haciendo que sea rápido y fácil hacer coincidir entradas específicas con sus categorías correspondientes. Para desbloquear esta poderosa herramienta y unir sin esfuerzo a Shawn con su clase, simplemente descargue e instale el complemento Kutools para Excely luego haga lo siguiente:

1. Seleccione la celda de destino donde desea mostrar la clase coincidente.
2. En Kutools pestaña, clic Ayudante de fórmula > Búsqueda y referencia > Indexar y hacer coincidir en varias columnas.
3. 
3. En el cuadro de diálogo emergente, haga lo siguiente:
   1. Haga clic en el 1er botón al lado de Buscar_col para seleccionar la columna que contiene la información clave que desea devolver, es decir, los nombres de las clases. (Aquí solo puede seleccionar una sola columna).
   2. Haga clic en el 2do. botón al lado de Tabla_rng para seleccionar las celdas para que coincidan con los valores en el seleccionado Buscar_col, es decir, los nombres de los estudiantes.
   3. Haga clic en el tercero botón al lado de Valor de búsqueda para seleccionar la celda que contiene el nombre del estudiante que desea hacer coincidir con su clase, en este caso, Shawn.
   4. Haga Clic en OK.
   5. 

Resultado

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

INDEX y COINCIDIR para buscar el primer valor que no esté en blanco

Para recuperar el primer valor que no esté en blanco, ignorando errores, de una columna o fila, puede usar una fórmula basada en las funciones ÍNDICE y COINCIDIR. Sin embargo, si no desea ignorar los errores de su rango, agregue la función ESBLANCO.

• Obtenga el primer valor que no esté en blanco en una columna o fila ignorando los errores:

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

• Obtenga el primer valor que no esté en blanco en una columna o fila, incluidos los errores:

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

  

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

Para recuperar el primer valor numérico de una columna o fila, utilice la fórmula basada en las funciones ÍNDICE, COINCIDIR e ESNUMERO.

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

INDEX y MATCH para buscar asociaciones MAX o MIN

Si necesita recuperar un valor asociado con el valor máximo o mínimo dentro de un rango, puede usar la función MAX o MIN junto con las funciones ÍNDICE y COINCIDIR.

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

Por ejemplo, si desea determinar quien tiene la puntuación más alta, emplea 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 encontrar el error #N/A cuando no hay ningún resultado coincidente. Por ejemplo, en la siguiente tabla, al intentar encontrar la puntuación de una estudiante llamada Samantha, aparece un error #N/A porque ella no está presente en el conjunto de datos.

Para que sus hojas de cálculo sean más fáciles de usar, puede personalizar este mensaje de error envolviendo su fórmula INDEX MATCH en la función IFNA:

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

Arriba está todo el contenido relevante relacionado con las funciones ÍNDICE y COINCIDIR en Excel. Espero que encuentres útil el tutorial. Si desea explorar más consejos y trucos de Excel, haga clic aquí. para acceder a nuestra extensa colección de más de miles de tutoriales.