Completar y buscar datos usando INDICE y COINCIDIR

Probablemente, en Excel, la función más usada para buscar y/o completar datos sea BUSCARV. Dicha función nos permite lo mencionado, pero con la limitación de que la referencia siempre debe de estar a la izquierda de nuestros datos. ¿Cómo podemos hacer si tenemos dos referencias y ellas incluso están no solo a la izquierda sino, por ejemplo, en una fila superior? Si bien podríamos pensar en anidar BUSCARV con otras funciones, en esta ocasión vamos a aprender a hacerlo usando INDICE y COINCIDIR.

Comencemos con INDICE. Microsoft nos dice que dicha función devuelve un valor o la referencia a un valor desde una tabla o rango. Hay dos formas de utilizar la función; la primera es si se desea devolver el valor de una celda especificada o de una matriz de celdas (Forma de matriz), y la segunda es si se desea devolver una referencia a las celdas especificadas (Forma de referencia). Para este caso la forma que nos interesa es la de matriz.

Microsoft nos dice lo siguiente sobre la forma de matriz de INDICE: Devuelve el valor de un elemento de una tabla o matriz, seleccionado por los índices de número de fila y de columna. Asimismo, la función tiene los siguientes argumentos:

INDICE(matriz; Núm_fila; [Núm_columna])

Argumento	Descripción
Matriz	Obligatorio. Es un rango de celdas o una constante de matriz.
Núm_fila	Obligatorio. Selecciona la fila de la matriz desde la cual devolverá un valor. Si se omite Núm_fila, se requiere Núm_columna.
Núm_columna	Opcional. Selecciona la columna de la matriz desde la cual devolverá un valor. Si se omite Núm_columna, se necesita Núm_fila.

Es decir, si en la función señalamos un rango de celdas o una matriz, e indicamos la fila y/o columna, nos devolverá el valor que coindice con esa fila/columna dentro del rango o matriz indicado. Para que se entienda mejor usaremos un par de ejemplos. Trabajaremos con datos que están en la hoja de la siguiente forma:

Entonces, si en cualquier celda colocamos esta fórmula:

=INDICE(B2:E8;2;2)

El resultado en la celda será de 400 ¿por qué? Porque en el rango de celdas que hemos usado (B2:E8) el valor correspondiente a la intersección de la segunda fila y segunda columna es 400 ¿se entendió? Vamos con un ejemplo más para que quede claro. Usemos ahora esta fórmula:

=INDICE(B2:E8;6;4)

El resultado es 80. Intersección de la sexta fila y la cuarta columna del rango de datos.

Ahora vamos con COINCIDIR. Microsoft nos dice que la función COINCIDIR busca un elemento determinado en un intervalo de celdas y después devuelve la posición relativa de dicho elemento en el rango. Por ejemplo, si el rango A1:A3 contiene los valores 5, 25 y 38, la fórmula =COINCIDIR(25,A1:A3,0) devuelve el número 2, porque 25 es el segundo elemento del rango. Para entender mejor dicha función, esta tiene los siguientes argumentos:

COINCIDIR(Valor_buscado, Matriz_buscada, [Tipo_de_coincidencia])

Argumento	Descripción
Valor_buscado	Obligatorio. Es el valor que desea buscar en Matriz_buscada.
Matriz_buscada	Obligatorio. Es el rango de celdas en que se realiza la búsqueda.
Tipo_de_coincidencia	Opcional. Puede ser el número -1, 0 o 1. El argumento Tipo_de_coincidencia específica cómo Excel hace coincidir el Valor_buscado con los valores de Matriz_buscada. El valor predeterminado de este argumento es 1 (1 = Menor que, 0 = Coincidencia exacta, -1 = Mayor que)

Si con los datos del ejemplo usamos la siguiente fórmula:

=COINCIDIR("Abraham";A2:A8;0)

El resultado que tendremos será 3, ya que mi nombre es está en la tercera posición del rango ingresado. Un ejemplo más:

=COINCIDIR(2016;B1:E1;0)

El resultado que tendremos será 2, ya que el 2016 ocupa la segunda posición en el rango ingresado.

Entonces ¿cómo aplicamos todo esto para buscar y/o completar datos? Veamos algunos ejemplos en donde anidemos dichas funciones en fórmulas que nos permitan cumplir dicha tarea.

Seguiremos trabajando con el mismo grupo de datos, pero ahora supongamos que queremos saber cuánto ganó Pedro el año 2017. Entonces usaremos una fórmula así:

=INDICE(B2:E8; COINCIDIR("Pedro";A2:A8;0);COINCIDIR(2017;B1:E1;0))

El resultado será 200 ¿por qué? Dentro de la función INDICE hemos reemplazado los argumentos Núm_fila y Núm_columna por COINCIDIR, ya que COINCIDIR nos trae el número de la posición del nombre “Pedro” dentro de un rango y el otro COINCIDIR usado hace lo mismo con el “2017” buscado. La fórmula por lo tanto se interpreta así:

=INDICE(B2:E8; 4; 3)

Un último ejemplo. Supongamos que tenemos dos listas de validación, una en la celda H2 basada en el rango de años y otra en la celda H3 basada en el rango de nombres y en la celda H3 la siguiente fórmula:

=INDICE(B2:E8;COINCIDIR(H3;A2:A8;0);COINCIDIR(H2;B1:E1;0))

De ese modo al elegir los dos valores en la lista correspondiente, en H3 tendremos siempre el resultado del monto en que coincidan dichos valores dentro de la matriz.

Espero les sea útil. Hasta la próxima.

Abraham Valencia

Descargue el ejemplo de aquí