Note: The other languages of the website are Google-translated. Back to English
Inicia sesión  \/ 
x
or
x
Regístrese  \/ 
x

or

Cómo usar ÍNDICE y COINCIDIR juntos en Excel

Al trabajar con tablas de Excel, es posible que encuentre constantemente situaciones en las que necesite buscar un valor. En este tutorial, le mostraremos cómo aplicar la combinación de las funciones INDICE y COINCIDIR para realizar búsquedas horizontales y verticales, búsquedas bidireccionales, búsquedas que distinguen entre mayúsculas y minúsculas y las búsquedas que cumplen con varios criterios.

¿Qué hacen las funciones INDICE y COINCIDIR en Excel?

Cómo utilizar las funciones ÍNDICE y COINCIDIR juntas


¿Qué hacen las funciones INDICE 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.

Uso de la función INDICE en Excel

El Í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:

=INDEX(array, row_num, [column_num])
  • 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 se omite row_num) se refiere al número de columna de la matriz.

Por ejemplo, para saber la puntuación del examen final de Jeff, el sexto estudiante de la lista, puede usar la función INDICE de esta manera:

= ÍNDICE (E2: E11, 6) >>> devuelve 60

coincidencia de índice de excel 01

√ Nota: el rango E2: E11 es donde aparece el examen final, 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 (B2: E2,3), ¿qué valor devolverá? --- Sí, volverá China,la 3rd valor en el rango dado.

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 el país de donde viene Emily con ÍNDICE, podemos ubicar el valor con un número de fila de 8 y un número de columna de 3 en las celdas de B2 a E11 así:

= ÍNDICE (B2: E11,8,3) >>> devuelve China

coincidencia de índice de excel 02

Según los ejemplos anteriores, sobre la función ÍNDICE en Excel, debe saber que:

  • La función INDICE puede trabajar con rangos verticales y horizontales.
  • La función INDICE no distingue entre mayúsculas y minúsculas.
  • El número de fila va delante del número de columna (si necesita ambos números) en la fórmula de ÍNDICE.

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.

Ahora, aprendamos primero sobre los conceptos básicos de la función COINCIDIR.


Uso de 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:

=MATCH(lookup_value, lookup_array, [match_type])
  • 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:

= COINCIDIR ("vera", C2: C11,0) >>> devuelve 4

coincidencia de índice de excel 03

√ Nota: La función COINCIDIR no distingue entre mayúsculas y minúsculas. El resultado “4” indica que el nombre “Vera” está en la cuarta posición de la lista. El "4" en la fórmula es el tipo de coincidencia que encontrará el primer valor en la matriz de búsqueda que sea exactamente igual al valor de búsqueda "Vera".

Saber la posición de la puntuación "96" en la fila de B2 a E2, puedes usar MATCH así:

= COINCIDIR (96, B2: E2,0) >>> devuelve 4

coincidencia de índice de excel 04

☞ Cosas que debemos saber sobre la función COINCIDIR en Excel:

  • La función COINCIDIR devuelve la posición del valor de búsqueda en la matriz de búsqueda, no el valor en sí.
  • La función COINCIDIR devuelve la primera coincidencia en caso de duplicados.
  • Al igual que la función INDICE, la función COINCIDIR también puede funcionar con rangos verticales y horizontales.
  • MATCH tampoco distingue entre mayúsculas y minúsculas.
  • Si el valor de búsqueda de la fórmula COINCIDIR está en forma de texto, enciérrelo entre comillas.

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 utilizar las funciones ÍNDICE y COINCIDIR juntas

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

Ejemplo para combinar INDICE y MATCH

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

Por ejemplo, para saber Puntaje del examen final de Evelyn, debemos usar la fórmula:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> devuelve 90

coincidencia de índice de excel 05

Bueno, dado que la fórmula puede parecer complicada, repasemos cada parte de ella.

coincidencia de índice de excel 06

Como puede ver arriba, el gran ÍNDICE fórmula contiene tres argumentos:

  • matriz: A2: D11 le dice a INDEX que devuelva el valor coincidente de las celdas a través A2 hasta D11.
  • row_num: COINCIDIR ("evelyn", B2: B11,0) le dice a INDEX la fila exacta del valor.
  • Acerca de la fórmula COINCIDIR, podemos explicarla como: devolver la posición del primer valor que es exactamente igual a "evelyn" en las celdas de B2 a B11 en un valor numérico, que es 5.
  • column_num: MATCH ("examen final", A1: D1,0) le dice a INDEX la columna exacta del valor.
  • Acerca de la fórmula COINCIDIR, podemos explicarla como: devolver la posición del primer valor que es exactamente igual a "examen final" en las celdas de A1 a D1 en un valor numérico, que es 4.

Entonces, puede ver la gran fórmula tan simple como la que mostramos a continuación:

= ÍNDICE (A2: D11,5,4)

En el ejemplo, usamos valores codificados, "evelyn" y "examen final". Sin embargo, en una fórmula tan grande, no queremos valores codificados, ya que tendremos que cambiarlos cada vez que busquemos algo nuevo. En tales circunstancias, podemos usar referencias de celda para hacer que la fórmula sea dinámica de esta manera:

= ÍNDICE (A2: D11,PARTIDO(G2, B2: B11,0),PARTIDO(F3, A1: D1,0))

coincidencia de índice de excel 07


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

Ahora, digamos que necesita conocer la clase de Evelyn, ¿cómo podemos usar ÍNDICE y COINCIDIR para saber la respuesta? Si prestó atención, debería notar que la columna de la clase está en el lado izquierdo de la columna del nombre, y que está más allá de la capacidad de otra poderosa función de búsqueda de Excel, VLOOKUP.

De hecho, la capacidad de búsqueda a la izquierda es uno de los aspectos en los que la combinación de INDICE y COINCIDIR es superior a BUSCARV.

Saber La clase de evelyn, todo lo que necesita hacer es cambiar el valor en la celda F3 a "Clase", y usar la misma fórmula que se muestra arriba, las funciones INDICE y COINCIDIR le dirán la respuesta de inmediato:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> devuelve A

coincidencia de índice de excel 08

Si tiene instalLED Kutools for Excel, un complemento de Excel profesional desarrollado por nuestro equipo, también puede aplicar una búsqueda a la izquierda para valores especificados con su BUSCAR de derecha a izquierda función con unos pocos clics. Para implementar la función, vaya a la Kutools pestaña en tu Excel, encuentra la Fórmula grupo y haga clic en BUSCAR de derecha a izquierda en la lista desplegable de Súper BUSCAR. Verá un cuadro de diálogo emergente como este:

coincidencia de índice de excel 09

Haga clic aquí para conocer los pasos concretos para aplicar la función de búsqueda izquierda con Kutools para Excel.


ÍNDICE y COINCIDIR para aplicar una búsqueda bidireccional

Ahora, ¿puede hacer la fórmula de combinación INDICE y COINCIDIR con valores de búsqueda dinámica para aplicar búsquedas bidireccionales? Practiquemos cómo hacer fórmulas en las celdas G3, G4 y G5 como se muestra a continuación:

coincidencia de índice de excel 10

Aquí están las respuestas:

Celda G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
Celda G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
Celda G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ Nota: Después de aplicar las fórmulas, puede obtener fácilmente la información de cualquier estudiante cambiando el nombre en la celda G2.


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

De los ejemplos anteriores, sabemos que las funciones INDICE y COINCIDIR no distinguen entre mayúsculas y minúsculas. Sin embargo, en los casos en que necesite su fórmula para distinguir caracteres en mayúsculas y minúsculas, puede agregar EXACT funcionan a sus fórmulas de esta manera:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ Nota: Esta es una fórmula de matriz que requiere que ingrese con Ctrl + Shift + Enter. A continuación, aparecerán un par de llaves en la barra de fórmulas.

Por ejemplo, para saber Puntaje del examen de JIMMY, usa las funciones como esta:

coincidencia de índice de excel 11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> devuelve 86

O puede usar referencias de celda:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> devuelve 86
Nota: No olvide ingresar con Ctrl + Shift + Enter.


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

Cuando se trata de una gran base de datos con varias columnas y títulos de fila, siempre es complicado encontrar algo que cumpla con múltiples condiciones. En este caso, consulte la fórmula siguiente para buscar varios criterios:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
Nota: Esta es una fórmula de matriz que requiere que ingrese con Ctrl + Shift + Enter. A continuación, aparecerán un par de llaves en la barra de fórmulas.

Por ejemplo, para encontrar el Puntaje del examen final de Coco de Clase A que es de India, la fórmula es la siguiente:

coincidencia de índice de excel 12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> devuelve 88
√ Nota: No olvide ingresar con Ctrl + Shift + Enter.

Bueno, ¿y si te olvidas constantemente de usar Ctrl + Shift + Enter completar la fórmula para que la fórmula devuelva resultados incorrectos? Aquí tenemos una fórmula más compleja, con la que puede completar con una simple Entrar llave:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

Para el mismo ejemplo anterior para encontrar el Puntaje del examen final de Coco de Clase A que es de India, la fórmula que solo necesita un habitual Entrar golpe es el siguiente:

coincidencia de índice de excel 13

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

Aquí no usaremos valores codificados, ya que querremos una fórmula universal en el caso de múltiples criterios. Solo de esta manera, podemos obtener fácilmente el resultado que queremos cambiando los valores en las celdas G2, G3, G4 en el ejemplo anterior.

Con Kutools for Excel's Función de búsqueda de múltiples condiciones, puede buscar valores específicos con múltiples criterios en unos pocos clics. Para implementar la función, vaya a la Kutools pestaña en tu Excel, encuentra la Fórmula grupo y haga clic en Búsqueda de varias condiciones en la lista desplegable de Súper BUSCAR. A continuación, verá un cuadro de diálogo emergente como se muestra a continuación:

coincidencia de índice de excel 14

Haga clic aquí para conocer los pasos concretos para aplicar la función de búsqueda de múltiples condiciones con Kutools para Excel.


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

Si tenemos una hoja de cálculo de Excel con diferentes columnas que comparten un título como se muestra a continuación, ¿cómo podemos hacer coincidir el nombre de cada estudiante con su clase con INDICE y MATCH?

coincidencia de índice de excel 15

Aquí, déjame mostrarte la forma de completar la tarea con nuestra herramienta profesional. Kutools for Excel. Con su Ayudante de fórmula, puede hacer coincidir rápidamente a los estudiantes con sus clases siguiendo los pasos que se muestran a continuación:

1. Seleccione la celda de destino donde desea aplicar la función.

2. Bajo la Kutools pestaña, ir a Ayudante de fórmula, haga clic Ayudante de fórmula en la lista desplegable.

coincidencia de índice de excel 16

Escoge Búsqueda desde el tipo de fórmula, luego haz clic en Indexar y hacer coincidir en varias columnas.

coincidencia de índice de excel 17

4. a. Haga clic en el 1er. icono de coincidencia de índice de Excelbotón en el lado derecho de Lookup_col para seleccionar las celdas de las que desea devolver un valor, es decir, los nombres de las clases. (Aquí solo puede seleccionar una sola columna o fila).
    B. Haga clic en el segundo icono de coincidencia de índice de Excelbotón en el lado derecho de Table_rng para seleccionar las celdas para que coincidan con los valores en el seleccionado Lookup_col, es decir, los nombres de los estudiantes.
    C. Haga clic en el tercero icono de coincidencia de índice de Excelbotón en el lado derecho de Valor de búsqueda para seleccionar la celda que se buscará, es decir, el nombre del estudiante que desea hacer coincidir con su clase.

coincidencia de índice de excel 18

5. Haga clic en Aceptar, verá el nombre de la clase de Jimmy en la celda de destino.

coincidencia de índice de excel 19

6. Ahora puede arrastrar el controlador de relleno hacia abajo para completar las clases de otros estudiantes.

coincidencia de índice de excel 20

Haga clic para descargar Kutools para Excel para una prueba gratuita de 30 días.



  • Barra de súper fórmula (edite fácilmente varias líneas de texto y fórmulas); Diseño de lectura (leer y editar fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y conservación de datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir celdas duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Varias celdas sin cambiar la referencia de la fórmula; Crear referencias automáticamente a varias hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertar rápidamente, Rangos, gráficos e imágenes; Cifrar celdas con contraseña; Crear lista de distribución y enviar correos electrónicos ...
  • Extraer texto, Agregar texto, Eliminar por posición, Quitar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtros a otras hojas); Orden avanzado por mes / semana / día, frecuencia y más; Filtro especial en negrita, cursiva ...
  • Combinar libros y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes de xls, xlsx y PDF...
  • Agrupación de tablas dinámicas por número de semana, día de la semana y más ... Mostrar celdas bloqueadas y desbloqueadas por diferentes colores; Resalte las celdas que tienen fórmula / nombre...
pestaña kte 201905
  • Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
officetab parte inferior
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.