Blog de Ninja ExcelFórmulas y funciones de ExcelINDICE y COINCIDIR: aprende a usarlas de forma sencilla

INDICE y COINCIDIR: aprende a usarlas de forma sencilla

Septiembre 6, 2021
Fórmulas y funciones de Excel
14 min read
Busqueda

Las funciones INDICE y COINCIDIR de Excel son unas herramientas muy populares ya que permiten buscar elementos en una tabla o rango y también se pueden combinar.  En este artículo aprenderás rápidamente cómo usarlas por separado y también, en conjunto. 

Información Clave:

La función INDICE permite encontrar valores que ocupan una posición en una tabla o lista.  La función COINCIDIR entrega la posición relativa que ocupa un valor dentro de una lista.  Al utilizar ambas funciones en conjunto, te convertirás en un verdadero Ninja de Excel.

Tip Ninja: Además de las funciones INDICE y COINCIDIR, te recomendamos otra herramienta de Exel que permite realizar búsquedas de valores en una matriz de datos, la famosa función BUSCARV. 

Función INDICE

La función INDICE de Excel permite encontrar valores en una matriz de datos determinando la fila y columna, es decir, devuelve el valor que se encuentra en la posición que le pedimos. 

Por ejemplo, si tenemos los ingresos y gastos de una empresa, podemos buscar de manera fácil y rápida cuáles fueron los gastos del mes de Mayo.

Tabla que muestra cómo funciona la función INDICE de Excel. En el ejemplo, nos muestra cúal es la matriz y el valor buscado

Como estamos el mes de mayo, queremos que Excel busque en esa fila, por lo tanto, es la fila número”5”.  También buscamos los gastos, que corresponden a la columna tres, por lo tanto, la columna es la número “3”. 

Recuerda que Excel le pone números imaginarios a la matriz seleccionada, entonces no debemos guiarnos por lo números de filas o columnas de la hoja de Excel. 

Tabla que muestra cómo funciona la función INDICE de Excel, indica la fórmula utilizada y cómo se ve en la hoja de trabajo

Entonces, los elementos de la función son:

  • matriz: B3: D11
  • núm_fila: 5
  • núm_columna: 3

Es decir, la función INDICE es:

Fórmula que muestra cómo se utiliza la función INDICE de Excel

El resultado que obtendremos será:

Tabla que muestra el resultado que entrega la función INDICE de Excel.

Podemos comprobar que los gastos de mayo corresponden al resultado entregado por Excel. 

Si quieres aprender más sobre esta función, te recomendamos visitar el blog de la fórmula INDICE.

Función COINCIDIR

La función COINCIDIR de Excel permite encontrar la posición numérica de un elemento en una lista de valores, es decir, Excel nos entrega el número de la posición en que está el valor buscado. 

Existen tres tipos de coincidencias, la coincidencia exacta, menor que y mayor que.  Si quieres aprender más sobre esta función y cómo manejar los tipos de coincidencias, te recomendamos visitar la función COINCIDIR.

Continuando con el ejemplo, queremos ver cuál fue el mes en que hubo ingresos de $7.500.  Para esto, debemos utilizar el tiempo de coincidencia “coincidencia exacta”.

Tabla que muestra cómo se utiliza la función COINCIDIR de Excel, indica que matriz y el valor buscado

Como estamos buscando los ingresos, debemos indicarle a Excel que sólo esa columna es la utilizada, entonces la matriz buscada corresponde a la columna de los ingresos.  Además, como es una coincidencia exacta, el tipo_de:_coincidencia es “0”.

Tabla que muestra cómo funciona la función COINCIDIR de Excel, indica la fórmula utilizada y cómo se ve en la hoja de trabajo

Los elementos de la función son:

  • valor_buscado:  2800
  • matriz_buscada:  C3:C9
  • tipo_de_coincidencia: 0

Es decir, la función COINCIDIR es:

Fórmula que muestra cómo se utiliza la función COINCIDIR de Excel con coincidencia exacta

El resultado que obtendremos será:

Tabla que muestra el resultado que entrega la función COINCIDIR de Excel.

Podemos comprobar que la posición número 5 de la columna de ingresos es la fila del mes de mayo, es decir, el mes que hubo $7.500 en ingresos, fue mayo. 

Combinar las funciones INDICE y COINCIDIR

Al usar ambas funciones en conjunto te convertirás en un verdadero ninja de Excel.  Esta herramienta permite encontrar valores de manera más rápida y dinámica en una base de datos.

Para combinarlas correctamente, debemos incluir la función COINCIDIR dentro de la función INDICE.  La función COINCIDIR se puede usar en el número de filas, en el número de columnas o en ambos argumentos de la función INDICE.  Veamos cada caso.

COINCIDIR determina la fila que utiliza INDICE

En este caso, la función COINCIDIR determinará la fila en que debe buscar la función INDICE, es decir, se encontrará en el argumento núm_fila.

Por ejemplo, queremos buscar cuáles fueron los gastos del mes de abril.

Tabla que muestra cómo se utiliza la función INDICE y COINCIDIR de Excel en conjunto, indica que matriz se utiliza y el valor buscado

Para esto, la función COINCIDIR busca el valor llamado “Abril” en la lista de los meses, además nos encontramos frente a coincidencia exacta, entonces en el argumento tipo_de_coincidencia debemos escribir un “0”.  También, como buscamos los gastos debemos indicar que es la columna númer 3.

Recuerda que como estamos buscando un valor de texto, debe ir entre comillas. 

Tabla que muestra cómo es la fórmula para usar en conjunto las funciones INDICE y COINCIDIR de Excel. Muestra la matriz usada y los valores que se buscan. Usamos COINCIDIR para indicar el número de fila

Los elementos de la función son:

  • matriz:  B3:D11
  • núm_fila:  Función COINCIDIR:
    • valor_buscado:  “Abril”
    • matriz_buscada:  B3:B11
    • tipo_de_coincidencia:  0
  • núm_columna: 3

Es decir, la función INDICE es:

Fórmula que muestra como se utiliza la función INDICE y COINCIDIR de Excel en conjunto. COINCIDIR se utiliza en el argumento de num_fila

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido por usar la funcion INDICE y COINCIDIR de Excel en una misma fórmula. Usamos COINCIDIR en el argumento num_fila

Podemos ver que el resultado entregado por Excel coincide con los gastos generados en abril.

COINCIDIR determina la columna que utiliza INDICE

En este caso, la función COINCIDIR determinará la columna en que debe buscar la función INDICE, es decir, se encontrará en el argumento núm_ columna.

Por ejemplo, queremos buscar cuáles fueron los ingresos del mes de septiembre.

Tabla que muestra cómo se utiliza la función INDICE y COINCIDIR de Excel en conjunto, indica que matriz se utiliza y el valor buscado.

Para esto, la función COINCIDIR busca el valor llamado “Ingresos” en la fila de las categorías, además nos encontramos frente a una coincidencia exacta, es decir, el argumento tipo_de_coincidencia es “0”.   También como buscamos el mes de septiembre, corresponde a la fila número “9”.

Tabla que muestra cómo es la fórmula para usar en conjunto las funciones INDICE y COINCIDIR de Excel. Muestra la matriz usada y los valores que se buscan. Usamos COINCIDIR para indicar el número de columna

Los elementos de la función son:

  • matriz:  B3:D11
  • núm_fila:  9
  • núm_columna:  Función COINCIDIR:
    • valor_buscado:  “Ingresos”
    • matriz_buscada:  B2:D2
    • tipo_de_coincidencia:  0

Es decir, la función INDICE es:

Fórmula que muestra como se utiliza la función INDICE y COINCIDIR de Excel en conjunto. COINCIDIR se utiliza en el argumento de num_columna

Lo que quiere decir es: “COINCIDIR le dirá en que columna se encuentran los ingresos y luego INDICE intersectará esa columna con la fila 9”

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido por usar la funcion INDICE y COINCIDIR de Excel en una misma fórmula. Usamos COINCIDIR en el argumento num_columna

Podemos ver que el resultado entregado por Excel coincide con los ingresos generados en septiembre.

COINCIDIR determina la fila y columna que utiliza INDICE

En este caso, la función COINCIDIR determinará la fila y columna en que debe buscar la función INDICE, es decir, se encontrará en el argumento núm_fila y núm_ columna.

Por ejemplo, queremos ver cuáles fueron los gastos de julio.

Tabla que muestra cómo se utiliza la función INDICE y COINCIDIR de Excel en conjunto, indica que matriz se utiliza y el valor buscado.

Para el número de fila, la función COINCIDIR buscará el valor “Julio” y para el número de columna, la segunda función buscará el valor “Gastos”.  En ambas funciones utilizamos el tipo de coincidencia exacta. 

Tabla que muestra cómo es la fórmula para usar en conjunto las funciones INDICE y COINCIDIR de Excel. Muestra la matriz usada y los valores que se buscan. Usamos COINCIDIR para indicar el número de fila y columna

Los elementos de la función son:

  • matriz:  B3:D11
  • núm_fila:  Función COINCIDIR:
    • valor_buscado:  “Julio”
    • matriz_buscada:  B3:B11
    • tipo_de_coincidencia:  0
  • núm_columna:  Función COINCIDIR:
    • valor_buscado:  “Gastos”
    • matriz_buscada:  B2:D2
    • tipo_de_coincidencia:  0

Es decir, la función INDICE es:

Fórmula que muestra como se utiliza la función INDICE y COINCIDIR de Excel en conjunto. COINCIDIR se utiliza en el argumento de num_columna y num_fila

Lo que quiere decir es: “Primero, COINCIDIR le dirá en que fila se encuentra el mes de Julio.  Segundo, le dirá en que columna se encuentran los gastos.  Por último, INDICE intersectará la fila y columna entregando el resultado que corresponde”

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido por usar la funcion INDICE y COINCIDIR de Excel en una misma fórmula. Usamos COINCIDIR en el argumento num_columna y num_columna

Podemos ver que el resultado entregado por Excel coincide con los gastos generados en julio.

Errores comunes

* #¡REF!

Este error sucede cuando los argumentos de la función INDICE indican una celda que no existe.  En este caso, te recomendamos revisar que el número de fila o columna existan. 

* #N/D

Este error sucede cuando la función COINCIDIR no encuentra alguna coincidencia. 

* #¿NOMBRE?

Este error suele indicar que hay un error en la fórmula COINCIDIR.  Te aconsejamos revisar los siguientes puntos:

  1. Revisar que si el valor buscado es un texto, que se encuentre entre comillas. 
  2. Asegúrate que el valor buscado exista en la lista de valores.
Tabla que muestra los errores comunes en las funciones INDICE y COINCIDIR de Excel

Tip Ninja:  Puedes utilizar las funciones SI.ERROR o SI.ND para controlar los errores.

Si quedaste con alguna duda, te recomendamos el siguiente video que muestra cómo funciona la función INDICE, la función COINCIDIR y como ambas funcionan en conjunto.

¿Aún te quedan dudas de la función COINCIDIR?…

COINCIDIR: aprende a utilizarla rápidamente

La función COINCIDIR de Excel es una herramienta muy popular porque entrega la posición que ocupa un elemento determinado en una lista de valores, además esta fórmula ofrece tres tipos de coincidencias.  En este artículo aprenderás a utilizarla rápidamente.

Información clave:

La función COINCIDIR permite encontrar la posición relativa de un valor en una fila, columna o tabla, es decir, esta fórmula busca un elemento y nos entrega su posición.

Tip Ninja:  Generalmente la función COINCIDIR se utiliza en conjunto con la función INDICE, haciendo la búsqueda de valores más rápida y dinámica, te recomendamos visitar INDICE y COINCIDIR.

Lo básico

  • Finalidad: La función COINCIDIR permite encontrar la posición relativa de un valor buscado en una matriz de valores.     
  • Características: Esta función permite tres formas de búsqueda: con una coincidencia exacta, mayor o menor.
  • Sintaxis: =COINCIDIR(valor_buscado; matriz_buscada; [tipo_de_coincidencia])
  • Argumentos:
    • valor_buscado: Valor o texto que queremos encontrar. 
    • matriz_buscada: Rango de celdas en donde se encuentran los valores.
    • [tipo_de_coincidencia]:  Especifica cómo Excel hace coincidir el valor_buscado en la matriz_buscada.  Hay 3 tipos: coincidencia exacta, menor que y mayor que.  Este argumento es opcional, si no se determina, Excel hará una coincidencia “menor que” automáticamente.

Utilizando la función COINCIDIR

La función COINCIDIR de Excel permite encontrar la posición relativa de un valor buscado en una fila, columna o tabla de valores determinada. 

Para entender mejor el funcionamiento de COINCIDIR, veamos un simple ejemplo.  Tenemos una tabla con dos columnas y cinco filas.  A través de la función, podemos encontrar la posición de cualquier valor.  Por ejemplo, queremos saber cuál es la posición de la letra “D”. 

Ejemplo simple que muestra como funciona la función COINCIDIR de Excel

Tin Ninja: Debemos considerar que la función COINCIDIR enumera las filas y columnas de acuerdo a los datos elegidos, por lo que la fila “1” corresponde a la que tiene el A y el 9, es decir, no debemos guiarnos por las filas y columnas de la hoja de Excel.

En simples palabras, la función COINCIDIR buscará el valor en el rango de valores determinado desde arriba hacia abajo, respondiendo la posición en la que se encuentra. 

Tabla que muestra como busca la función COINCIDIR de Excel

Por lo tanto, como buscamos el elemento “D” debemos indicar que las letras se encuentran en la columna “1”, Excel buscará “D” en la columa “1” desde arriba hacia abajo y nos responderá la posición en la que se encuentra.

Tabla que muestra el ejemplo simple de cómo funciona la función COINCIDIR de Excel, muestra el valor buscado y la matriz utilizada.

Entonces, los elementos de la función COINCIDIR serán:

  • valor_buscado: “D” 
  • matriz_buscada: B2:B6 
  • [tipo_de_coincidencia]: 0

Tip Ninja: Si el valor_buscado es un valor de texto debemos escribirlo entre comillas, de otra forma Excel no lo reconocerá. 

Por lo tanto, el resultado que obtendremos será:

Tabla que muestra el resultado obtenido del ejemplo simple de la función COINCIDIR

Es decir, el elemento D se posiciona en la fila número “4” de la columna de las letras.

La función COINCIDIR de Excel ofrece tres tipos de coincidencia, haciendo más flexible la búsqueda, estos son: la coincidencia exacta, menor que y mayor que.  También podemos hacer una búsqueda aproximada.  Veamos cada caso.

Coincidencia exacta

Para este tipo de coincidencia debemos escribir un “0” en el argumento tipo_de_coincidencia.  Excel encontrará el primer valor que sea exactamente igual al valor buscado. 

Por ejemplo, deseamos conocer cuál fruta tiene un precio de $2.000 por Kilo. 

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia exacta.

Como buscamos el precio por kilo, debemos seleccionar la matriz que contenga sólo los precios.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia exacta. Muestra la matriz utilizada y la formula que usamos

Los elementos de la función son:

  • valor_buscado:  2000
  • matriz_buscada:  D3:D9
  • tipo_de_coincidencia: 0

Es decir, la función COINCIDIR es:

Formula que muestra cómo se utiliza la función COINCIDIR de Excel con una coincidencia exacta

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido del ejemplo de la función COINCIDIR de Excel con una coincidencia exacta

Vemos que la fruta que tiene un precio de $2.000/Kg se encuentra en la fila número “6”, por lo tanto, el kiwi es la fruta que tiene un precio de $2.000 el kilo. 

¿Qué pasa si hay dos frutas con el mismo precio?, ¿Cuál posición responde Excel? La función COINCIDIR responde la primera coincidencia exacta que encuentra.  Por ejemplo, el plátano y el pomelo tienen el mismo precio por kilo, $1.200, pero como el primer valor $1.200 se encuentra en la fila “1”, Excel responderá 1. 

Muestra cómo funciona la función COINCIDIR de Excel con coincidencia exacta cuando hay dos valores iguales.

Coincidencia exacta con comodín

Cuando establecemos un tipo de coincidencia exacta podemos utilizar comodines.  Un comodín es un carácter especial que permite realizar coincidencias similares.  Excel interpreta el comodín “?” como cualquier carácter e interpreta el comodín “*” como cualquier secuencia de caracteres, esto nos permite realizar búsquedas con algunos valores específicos.   

Por ejemplo, queremos buscar un código de las frutas que comience con “CD” pero no nos importan los otros elementos, esto quiere decir que Excel debe buscar el código que comience con “CD” sin importar el resto del código. 

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia exacta con uso de comodín en el valor buscado

Como todos los códigos son de 4 valores y sólo queremos los dos primeros, el valor_buscado será de la siguiente forma “CD**”. 

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia exacta usando comodines en el valor buscado. Muestra la matriz utilizada y la formula que usamos.

Los elementos de la función son:

  • valor_buscado:  “CD**”
  • matriz_buscada:  B3:B9
  • tipo_de_coincidencia: 0

Es decir, la función COINCIDIR es:

Formula que muestra cómo se utiliza la función COINCIDIR de Excel con una coincidencia exacta con el uso de comodín en el valor buscado

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido del ejemplo de la función COINCIDIR de Excel con una coincidencia exacta con el uso de comodín en el valor buscado

Por lo tanto, la fruta que tiene el código que comienza con “CD” es la pera. 

Menor que

Para este tipo de coincidencia debemos escribir un “1” en el argumento tipo_de_coincidencia.  Excel encontrará el primer valor que sea menor o igual al valor buscado.  Para esta coincidencia, la lista de elementos debe estar ordenada de forma ascendente, es decir, de menor a mayor.

Por ejemplo, deseamos conocer cuál fruta tiene un precio menor a $1.800 por kilo.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia menor que

Como estamos buscando un precio menor a $1.800 por kilo, debemos seleccionar la columna que contenga sólo los precios.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia menor que. Muestra la matriz utilizada y la formula que usamos

Los elementos de la función son:

  • valor_buscado:  1800
  • matriz_buscada:  D3:D9
  • tipo_de_coincidencia: 1

Es decir, la función COINCIDIR es:

Formula que muestra cómo se utiliza la función COINCIDIR de Excel con una coincidencia menor que

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido del ejemplo de la función COINCIDIR de Excel con una coincidencia menor que

Excel respondió la posición 5, es decir, la manzana es la fruta que tiene un precio por kilo menor a $1.800.  Podemos ver que la manzana no es la única fruta con precio menor a $1.800, pero Excel responderá el primer valor que sea más cercano al determinado.

Mayor que

Para este tipo de coincidencia debemos escribir un “-1” en el argumento tipo_de_coincidencia.  Excel encontrará el primer valor que sea mayor o igual al valor buscado.  Para esta coincidencia, la lista de elementos debe estar ordenada de forma descendente, es decir, de mayor a menor.

Por ejemplo, deseamos conocer cuál fruta tiene un precio mayor a $1.900 por kilo.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia mayor que

Como estamos buscando un precio mayor a $1.900 por kilo, debemos seleccionar la columna que contenga sólo los precios.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia mayor que. Muestra la matriz utilizada y la formula que usamos

Los elementos de la función son:

  • valor_buscado:  1900
  • matriz_buscada:  D3:D9
  • tipo_de_coincidencia: -1

Es decir, la función COINCIDIR es:

Formula que muestra cómo se utiliza la función COINCIDIR de Excel con una coincidencia mayor que

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido del ejemplo de la función COINCIDIR de Excel con una coincidencia mayor que

Excel respondió la posición 2, es decir, el kiwi es la fruta que tiene un precio por kilo mayor a $1.900.  Podemos ver que el kiwi no es la única fruta con precio menor a $1.800, pero Excel responderá el primer valor que sea más cercano al determinado.

Búsqueda aproximada

La función COINCIDIR también permite realizar búsquedas aproximadas.  Para esto, usamos la coincidencia menor que.  Por ejemplo, deseamos buscar un código similar a “CTOF”.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia aproximada

Como estamos buscando un código debemos seleccionar sólo esa columna, sin incluir la fruta y el precio.

Tabla que muestra cómo utilizar la función COINCIDIR de Excel con el tipo de coincidencia aproximada. Muestra la matriz utilizada y la formula que usamos

Los elementos de la función son:

  • valor_buscado:  “CTOF”
  • matriz_buscada:  B3:B9
  • tipo_de_coincidencia: 1

Es decir, la función COINCIDIR es:

Formula que muestra cómo se utiliza la función COINCIDIR de Excel con una coincidencia aproximada

El resultado que obtendremos será:

Tabla que muestra el resultado obtenido del ejemplo de la función COINCIDIR de Excel con una coincidencia aproximada

Excel buscó una coincidencia aproximada entregando el código CDEF, el cual tiene sólo dos letras diferentes a CTOF.

Matriz horizontal y vertical

La función COINCIDIR de Excel puede utilizarse en matrices verticales y horizontales.  Por ejemplo, si queremos saber que posición ocupa el membrillo en la lista de frutas, obtendremos el mismo resultado si la lista es vertical o horizontal.

Muestra cómo funciona la función COINCIDIR de Excel cuando tenemos una lista vertical o horizontal

Como buscamos el membrillo, estamos frente a una coincidencia exacta.  Además, al ser una búsqueda de texto debemos escribirla entre comillas para que Excel pueda reconocerlo.

Muestra la diferencia de la función COINCIDIR de Excel cuando tenemos una lista vertical y otra horizontal. Muestra las matrices utilizadas y el valor buscado

Es decir, la función COINCIDIR es:

En la lista vertical: Fórmula que se utiliza para la función COINCIDIR de Excel con una matriz vertical

En la lista horizontal: Fórmula que se utiliza para la función COINCIDIR de Excel con una matriz horizontal

El resultado que obtendremos será:

Muestra la diferencia de la función COINCIDIR de Excel cuando tenemos una lista vertical y otra horizontal. Muestra los resultados obtenidos

Podemos ver que en ambas listas obtenemos la misma posición relativa del membrillo.    

Errores comunes

* #N/D

Este error sucede cuando la función COINCIDIR no puede encontrar ninguna coincidencia o cuando la matriz seleccionada no es de una sola fila o columna.

* #¿NOMBRE?

Este error suele indicar que hay un error en la fórmula COINCIDIR.  Te aconsejamos revisar los siguientes puntos:

1. Revisar que si el valor buscado es un texto, que se encuentre entre comillas. 

2. Asegúrate que el valor buscado exista en la lista de valores.

Tabla que muestra los errores comunes de la función COINCIDIR de Excel como #N/D y #¿NOMBRE?

Tip Ninja:  Puedes utilizar las funciones SI.ERROR o SI.ND para controlar los errores.  

Fernanda es analista de estrategia y desarrollo, se formó como ingeniera comercial en la Universidad Católica de Chile y también como profesora de matemáticas en la Universidad de los Andes.

You may also like

Evalúa a tus colaboradores