INDICE – COINCIDIR – Excel y Google Sheets

Este tutorial le enseñará a utilizar la combinación INDICE & COINCIDIR para realizar búsquedas en Excel y Google Sheets.

 

INDICE & COINCIDIR, la pareja perfecta

Veamos con más detalle algunas de las formas de combinar las funciones INDICE y COINCIDIR. La función COINCIDIR está diseñada para devolver la posición relativa de un elemento dentro de un array, mientras que la función INDEX puede obtener un elemento de un array dada una posición específica. Esta sinergia entre las dos funciones permite realizar casi cualquier tipo de búsqueda que puedas necesitar.

La combinación INDICE / COINCIDIR se ha utilizado históricamente como reemplazo de la función BUSCARV. Una de las principales razones es la capacidad de realizar una búsqueda con la izquierda (ver la siguiente sección).

Nota: la nueva función BUSCARX ahora puede realizar búsquedas a la izquierda.

Búsqueda a la izquierda

Utilicemos esta tabla de estadísticas de baloncesto:

Queremos encontrar el número de jugador de Bob. Como el número de jugador está a la izquierda de la columna del nombre, no podemos usar un BUSCARV. En su lugar, podríamos hacer una petición COINCIDIR básica para calcular la fila de Bob

=COINCIDIR(H2; B2:B5; 0)

Esto buscará una coincidencia exacta de la palabra «Bob», por lo que nuestra función devolvería el número 2, ya que «Bob» está en la 2da posición. A continuación podemos utilizar la función INDICE para devolver el número de jugador correspondiente a una fila. Por ahora, vamos a introducir manualmente «2» en la función:

=INDICE(A2:A5; 2)

Aquí, INDICE hará referencia a A3, ya que es la 2da celda dentro del rango A2:A5 y devolverá el resultado de 42. Para nuestro objetivo general, podemos entonces combinar estos dos en:

=INDICE(A2:A5;COINCIDIR(H2;B2:B5;0))

El beneficio aquí es que pudimos devolver un resultado de una columna a la izquierda de donde estábamos buscando.

Búsqueda en dos dimensiones

Veamos nuestra tabla de antes:

Esta vez, sin embargo, queremos obtener una estadística específica. Hemos dicho que queremos buscar Rebotes en la celda H1. En lugar de tener que escribir varias sentencias SI para determinar de qué columna obtener el resultado, puedes volver a utilizar una función MATCH.

La función INDEX te permite especificar el valor de la fila y el valor de la columna. Vamos a añadir otra función COINCIDIR aquí para determinar qué columna queremos. Esto se verá como

COINCIDIR(H1;A1:E1;0)

Nuestra celda en H1 es un desplegable que nos permite elegir la categoría que queremos buscar, y luego nuestro COINCIDIR determina a qué columna de la tabla pertenece. Introduzcamos esta nueva parte en nuestra fórmula anterior.

Obsérvese que tenemos que modificar el primer argumento para que sea de dos dimensiones, ya que no sólo queremos un resultado de la columna A.

=INDICE(A2:E5;COINCIDIR(H2;B2:B5;0);COINCIDIR(H1;A1:E1;0))

 

En nuestro ejemplo, queremos encontrar Rebotes para Charlie. Nuestra fórmula va a evaluar esto así

=INDICE(A2:E5; COINCIDIR("Charlie"; B2:B5; 0); COINCIDIR("Rebotes"; A1:E1; 0))
=INDICE(A2:E5; 3; 4)
=D4
=6

Ahora hemos creado una configuración flexible que permite al usuario obtener cualquier valor que desee de nuestra tabla sin tener que escribir múltiples fórmulas o sentencias SI de ramificación.

Múltiples secciones

No se utiliza a menudo, pero INDICE tiene un quinto argumento que se puede dar para determinar qué área dentro del argumento uno a utilizar. Esto significa que necesitamos una forma de pasar múltiples áreas al primer argumento. Puede hacerlo utilizando un conjunto extra de paréntesis.

Este ejemplo ilustrará cómo puede obtener resultados de diferentes tablas en una hoja de cálculo utilizando INDICE. Este es el esquema que utilizaremos.

Tenemos estadísticas para tres trimestres diferentes de juego.

 

En las celdas H1:H3, hemos creado listas desplegables de Validación de Datos para nuestras distintas opciones. El desplegable para el trimestre viene de J2:J4. Usaremos esto para otra sentencia COINCIDIR, para determinar qué área usar. Nuestra fórmula en H4 se verá así

=INDICE((A3:E6; A10:E13; A17:E20); COINCIDIR(H2; B3:B6; 0); MATCH(H1; A2:E2; 0); COINCIDIR(H3; J2:J4; 0))

Ya hemos visto cómo funcionan las dos funciones MATCH interiores, así que vamos a centrarnos en el primer y el último argumento:

=INDICE((A3:E6; A10:E13; A17:E20); ...; COINCIDIR(H3; J2:J4; 0))

Hemos dado a la función INDICE múltiples matrices en el primer argumento encerrándolas todas entre paréntesis. La otra forma de hacer esto es usando Fórmulas – Definir Nombre.

Podría definir un nombre llamado «MisTablas» con una definición de:

='Secciones múltiples'!$A$3:$E$6;'Secciones múltiples'!$A$10:$E$13;'Secciones múltiples'!$A$17:$E$20

De esta manera, obtenemos la siguiente fórmula

=INDICE(MyTable;COINCIDIR(H2;Table134[Nombre];0);
COINCIDIR(H1;Table134[#Encabezados];0);COINCIDIR(H3;J2:J4;0))

 

 

Volvamos a la declaración completa. Nuestras diversas funciones COINCIDIR van a decirle a la función INDICE exactamente dónde buscar. Primero, determinaremos que «Charlie» es la 3era fila. A continuación, queremos «Rebotes», que es la 4ta columna. Finalmente, determinamos que queremos el resultado de la 2ª tabla. La fórmula se evaluará a través de este como:

=INDICE((A3:E6; A10:E13; A17:E20); COINCIDIR(H2; B3:B6; 0); COINCIDIR(H1; A2:E2; 0); COINCIDIR(H3; J2:J4; 0))
=INDICE((A3:E6; A10:E13; A17:E20); 3; 4; 2)
=INDICE(A10:E13; 3; 4)
=D13
=14

Como mencionamos al principio de este ejemplo, estás limitado a que las tablas estén en la misma hoja de cálculo. Si puedes escribir formas correctas de decirle a tu INDICE de qué fila, columna y/o área quieres recuperar datos, INDICE te servirá muy bien.

Google Sheets -INDICE & COINCIDIR

Todos los ejemplos anteriores funcionan exactamente igual en Google Sheets que en Excel.