Cómo usar INDICE y COINCIDIR en lugar de BUSCARV

Cuando necesitamos traer datos en Excel nos apoyamos en la función BUSCARV.  
Sin embargo tiene una serie de limitaciones que hacen que no sea una función adecuada para todos los casos: 

• La información debe estar estructurada de determinada manera para usarla: los datos a traer deben estar a la derecha del valor a buscar. No realiza búsquedas hacia la izquierda
• No podemos insertar o eliminar columnas porque la función se rompe o trae datos erróneos.

Estas situaciones las podemos resolver con la combinación de funciones INDICE y COINCIDIR.  

En este video-tutorial te explico cómo usarlas:

Descarga la plantilla utilizada en el video aquí

Explicación

A la hora de buscar datos o hacer cruces de información, lo primero que pensamos es acudir en BUSCARV, dado que es una de las funciones más conocidas, sencillas y usadas en Excel.

Sin embargo, esta función tiene algunas limitaciones que hacen que resulte dificultoso en algunas ocasiones obtener los resultados deseados de forma eficiente.

¿Cuáles son estas limitaciones?

• Sólo podemos hacer búsquedas hacia la derecha.

• No podemos agregar o eliminar columnas en los rangos que utiliza BUSCARV porque puede arrojar error o traer el dato incorrecto

Cuando pasa esto, recurrimos a 2 funciones que anidadas nos van a ayudar a superar estas limitaciones de BUSCARV.

Estas funciones son: INDICE y COINCIDIR.

Función INDICE:

INDICE es una función que se encuentra dentro de la categoría las funciones de búsqueda y referencia, la cual tiene como propósito traer el valor de una celda dentro de una matriz, a partir de un número de fila y un número de columna que debemos proporcionar. 

Esta función tiene dos formas en su uso, la forma matricial y la forma de referencia, sin embargo, en esta ocasión solo revisaremos la primera (forma matricial).

Sintaxis función INDICE forma matricial

= INDICE (matriz; núm_fila; [núm_columna])

1. Matriz (obligatorio): es la matriz o rango de celdas que contiene los datos que necesitamos buscar. Esta matriz puede estar compuesta por una o varias columnas, o bien, por una o varias filas, todo dependerá de lo que busquemos.
2. Núm_fila (obligatorio): es el número de fila donde se encuentra el valor que buscamos, dentro de la matriz seleccionada.
3. Núm_columna (opcional): es el número de columna donde se encuentra el valor que buscamos, dentro de la matriz seleccionada.

Revisemos un ejemplo:

Necesitamos saber el precio que se encuentra en la intersección de la fila 5 y la columna 3, dentro del rango C9:E16, correspondiente a un listado de bebidas:

=INDICE(C9:E16;5;3)

En la celda H12 ingresamos la función INDICE, la cual nos arrojó el valor que se encuentra en dicha intersección, vale decir, 57.

Y, si nos fijamos, los números de fila y de columna que debemos incluir en la función INDICE, no corresponden a los números propios de la hoja de Excel, sino que estos hacen referencia a los números de filas y de columnas de la matriz que seleccionamos (C9:E16):

Como podemos ver, INDICE es una función que nos proporciona rápidamente el dato que necesitamos con tan solo indicarle las coordenadas correctas.

Pero, si necesitáramos que dichas coordenadas fueran dinámicas, es decir, que las podamos obtener de manera automática ¿cómo lo hacemos?

Ahí entra en juego la función COINCIDIR, pero revisemos primero cómo opera esta función en forma independiente.

Función COINCIDIR

COINCIDIR también forma parte de las funciones de búsqueda y referencia, la cual tiene como cometido buscar un valor dentro de una matriz, fila o columna, arrojando como resultado la posición relativa de dicho valor. En palabras más simples, la función nos dirá en qué número de fila o columna se ubica el dato que buscamos dentro del rango seleccionado.

Sintaxis función COINCIDIR

=COINCIDIR(valor_buscado,matriz_buscada, [tipo_de_coincidencia])

1. Valor buscado (obligatorio): Es el valor que deseamos buscar en matriz_buscada.
2. Matriz buscada (obligatorio): Es el rango de celdas en que se realiza la búsqueda.
3. Tipo de coincidencia (opcional): Es un número que señala el tipo de coincidencia en la búsqueda:
   1. Si indicamos 0, encuentra el primer valor que es exactamente igual que el valor_buscado.
   2. Si indicamos 1 o lo omitimos, encuentra el mayor valor que es menor o igual que el valor_buscado.
   3. Si indicamos -1, encuentra el valor más pequeño que es mayor o igual que el valor_buscado.

Revisemos un ejemplo:

Tenemos el mismo listado de bebidas y precisamos saber qué número de fila ocupa la bebida “Ponche de Huevo” dentro del rango C9:C16:

=COINCIDIR(I8;C9:C16;0)

En la celda I9 ingresamos la función COINCIDIR, donde indicamos que el valor a buscar será “Ponche de Huevo” (celda I8), la matriz buscada será el rango C9:C6 (listado de bebidas) y finalmente le señalamos que la coincidencia sea igual al valor buscado, es decir, exacta, por lo que escribimos 0.

El resultado será 5, dado que el valor buscado se encuentra en la fila 5 de la matriz C9:C16:

Y si quisiéramos saber el número de columna que ocupa un valor ¿cómo lo hacemos?

COINCIDIR también provee esa solución.

Ahora, necesitamos saber el número de columna que ocupa el valor “Pack de 24” dentro del rango D8:F8:

=COINCIDIR(I11;D8:F8;0)

En la celda I12 ingresamos la función COINCIDIR e indicamos como valor buscado “Pack de 24” (celda I11), la matriz buscada será el rango D8:F8 y finalmente, escribimos 0 para que la coincidencia sea exacta.

Como resultado nos arroja que el valor buscado se encuentra en la columna 3 de la matriz D8:F8:

El resultado que nos arroje la función dependerá siempre de la matriz que seleccionemos:

• Si escogemos un rango compuesto por varias filas y una sola columna, el resultado será el número de fila que ocupa el valor.
• Si seleccionamos un rango compuesto por varias columnas y una sola fila, el resultado será el número de columna que ocupa el valor.

Como ya conocemos el objetivo de la función INDICE y COINCIDIR de forma independiente, estamos en condiciones de anidarlas y ver cómo se potencia su utilidad.

Fórmula combinada INDICE + COINCIDIR

Ahora, teniendo en cuenta que la función COINCIDIR nos proporciona el número de fila o columna que ocupa un valor dentro de una matriz, podemos perfectamente utilizarla en los argumentos de Núm_fila o Núm_columna de la función INDICE, así logramos que estos componentes sean dinámicos y no tengamos que estar escribiéndolos directamente en la función.

Veámoslo con un ejemplo:

Se precisa saber la categoría a la cual pertenece la bebida “Llamarada de Moe”, vale decir, buscar dentro de la matriz el nombre de la bebida y traer el nombre de su categoría.

=INDICE(B9:B16;COINCIDIR(I8;C9:C16;0);)

En la celda I9 llamamos primero a la función INDICE, donde en la matriz debemos escoger el rango que contiene los datos que queremos obtener, es decir, la columna “Categoría” (rango B9:B16). Luego, el argumento “Núm_fila” lo reemplazamos por la función COINCIDIR, mediante la cual buscaremos el número de fila que ocupa el nombre de bebida que necesitamos. Entonces, en valor buscado señalamos la celda I8 (“Llamarada de Moe”), en matriz buscada seleccionamos la columna que contiene los nombres de las bebidas (rango C9:C16), luego, indicamos que la coincidencia sea de tipo exacta (0) y finalmente cerramos la función COINCIDIR. Ahora, como seleccionamos solo una columna en la matriz de INDICE, el dato de “Núm_columna” debe quedar vacío, porque no precisamos que se mueva a otra columna, por tanto, cerramos la función.

Si analizamos la fórmula empleada seleccionando la función y presionando la tecla F9, podremos observar que el resultado de COINCIDIR es 7, por tanto, INDICE buscará y traerá el valor que se encuentra en la fila 7 del rango B9:B16:

Por tanto, como resultado obtenemos que la categoría a la cual pertenece la bebida “Llamarada de Moe” es “Bebidas Alcohólicas”, y si miramos la tabla, el resultado es correcto:

Aquí podemos evidenciar que, mediante esta combinación de funciones, derribamos fácilmente la restricción de hacer búsquedas hacia la izquierda, de hecho, esta fórmula permite hacer búsquedas en cualquier dirección (vertical y horizontal), solo basta que se seleccionen correctamente los argumentos y tendremos excelentes resultados.

Y no olviden esto:

• La matriz de la función INDICE siempre será la columna o fila que posee los datos o valores que necesitamos traer, es como el número de columna de BUSCARV, pero en vez de indicar el número, seleccionamos el rango que la compone.
• Mediante COINCIDIR siempre buscaremos el número de columna o fila que ocupa el valor que disponemos para hacer la búsqueda, y lo localizaremos dentro del rango en que se encuentran enlistados dichos valores.
• Siempre los rangos de “matriz” de INDICE y la “matriz buscada” de COINCIDIR, deben tener la misma longitud, vale decir, la misma cantidad de filas o de columnas, de caso contrario, la fórmula arrojará error.

Y para finalizar, revisemos una utilidad extra de esta combinación de funciones, a fin de que observen su gran potencial.

Fórmula combinada INDICE+COINCIDIR para búsquedas bidimensionales.

Cuando precisamos buscar datos dentro de una matriz que cumplan dos criterios, y dichos criterios se ubican en filas y columnas ¿cómo lo hacemos?

La combinación de INDICE + COINCIDIR también nos permite realizar este tipo de búsquedas.

Revisemos un ejemplo:

Aquí tenemos nuevamente nuestra tabla que agrupa un listado de bebidas:

En la celda I11 (“Resultado”) debemos traer rápidamente el precio del producto que cumpla que sea igual “Buzz Cola” y que adicionalmente su presentación (Pack) sea “Pack de 24”.

=INDICE(D9:F16;COINCIDIR(I9;C9:C16;0);COINCIDIR(I10;D8:F8;0))

Si nos fijamos, los criterios que debe cumplir el producto por consultar se ubican en filas (nombres de las bebidas) y en columnas (presentaciones), por tanto, lo que haremos será sustituir los argumentos núm_fila y núm_columna de la función INDICE, por la función COINCIDIR, para que de forma dinámica nos proporcione las coordenadas que precisamos (número de fila y columna respectivamente).

Iniciamos con la función INDICE, la matriz de esta estará compuesta por todas las celdas que agrupan los precios de los productos (valor que queremos obtener), por ende, seleccionamos el rango D9:F16. El argumento núm_fila lo reemplazamos por la función COINCIDIR, la cual buscará el criterio que se encuentra ordenado en filas, vale decir, el nombre de la bebida. Indicamos como valor buscado “Buzz Cola” (celda I9), en matriz buscada seleccionamos el rango C9:C16 (matriz que agrupa los nombres de las bebidas), finalmente indicamos 0 (coincidencia exacta) y cerramos paréntesis. Ahora, el argumento núm_columna también lo sustituimos por la función COINCIDIR, la cual buscará el criterio que se encuentra ordenado en columnas, es decir, la presentación (Pack). Señalamos como valor buscado “Pack de 24” (celda I10), la matriz buscada será el rango D8:F8 (rango que agrupa todas las presentaciones), con coincidencia exacta (0) y cerramos paréntesis del segundo COINCIDIR y de INDICE.

Y vemos como rápidamente nos arrojó el precio correcto del producto, cumpliendo ambos criterios:

Por consiguiente, cuando precisemos hacer búsquedas tanto por filas como por columnas dentro una matriz, la combinación de INDICE más dos COINCIDIR será una eficiente solución.