Las funciones de búsqueda son ampliamente utilizadas en Excel y son de gran ayuda para obtener el valor buscado pero ninguna de ellas nos permite encontrar la dirección de una celda a partir de un valor. Es necesario utilizar una combinación de funciones en caso de que necesitemos encontrar dicha dirección y en esta ocasión revisaremos algunas alternativas para lograr esta tarea.
La función DIRECCION en Excel
La función clave de todos nuestros ejemplos será la función DIRECCION que por sí sola nos devuelve la referencia a una celda después de haber especificado la fila y columna que necesitamos. Considera la siguiente fórmula:
=DIRECCION(2, 1)
En esta fórmula estamos pidiendo a la función DIRECCION que nos devuelva la referencia a la celda que se encuentra en la fila 2 y en la columna 1 de nuestra hoja. En la siguiente imagen puedes ver el resultado de esta fórmula:
El contenido de la celda A2 no es relevante en esta fórmula ya que estamos solicitando la dirección de la celda ubicada en la fila 2 y columna 1. Es así como la función DIRECCION será de gran utilidad para obtener la dirección de cualquier celda después de que hayamos especificado la fila y la columna en la que se encuentra.
Dirección de celda a partir de un valor
Para nuestro ejemplo supondremos una tabla de datos que contiene una lista de nombres tal como se observa en la siguiente imagen:
Necesitamos construir una fórmula que nos permita obtener la dirección de la celda bajo la columna Nombre que contenga el valor indicado en la celda E1. Para lograr nuestro cometido utilizaremos la función COINCIDIR que nos devuelve la posición de un elemento dentro de un rango que coincida con el criterio de búsqueda especificado. Por ejemplo, la siguiente fórmula hará la búsqueda del nombre Luis:
=COINCIDIR(E1, A1:A21, 0)
El primer argumento es el término buscado, que en nuestro ejemplo se encuentra en la celda E1. El segundo argumento es el rango de búsqueda y que será nuestra lista de nombres y finalmente el valor cero le indica a la función que haga una búsqueda exacta del valor. En la siguiente imagen puedes ver el resultado de esta fórmula:
Nuestra fórmula devuelve el valor 5 que es la posición que ocupa la celda con el nombre Luis dentro del arreglo de búsqueda indicado. Para obtener la dirección de dicha celda será suficiente con indicar este valor a la función DIRECCION de la siguiente manera:
=DIRECCION(COINCIDIR(E1, A1:A21, 0), 1)
Observa que la fórmula anterior se ha convertido en el primer argumento de la función DIRECCION ya que es la encargada de devolvernos la fila y he indicado el valor 1 como el segundo argumento de la función DIRECCION porque la búsqueda la haremos sobre la columna A de nuestra hoja. A continuación puedes ver el resultado de esta fórmula:
Antes de continuar debo hacer algunas observaciones importantes sobre la formula recién creada. En primer lugar debes notar que el rango de búsqueda A1:A21 indicado en la función COINCIDIR comienza en la fila 1 y por lo tanto el resultado devuelto coincidirá también con el número de fila de nuestra hoja, pero no siempre tenemos una situación como esta. En ocasiones el rango de celdas de búsqueda comienza algunas filas por debajo de la primera fila y por lo tanto tendremos que considerar dicho desplazamiento en nuestra fórmula. Por ejemplo:
En este caso el rango de búsqueda comienza en la celda A4, lo que quiere decir que hay 3 filas entre la primera fila de nuestra hoja y dicho rango. Tal como lo ves en la barra de fórmulas de la imagen anterior, para obtener la dirección de la celda adecuadamente será necesario sumar el valor 3 al resultado de la función COINCIDIR.
Otra observación importante de nuestra fórmula es que siempre devolverá la dirección de la celda de la primera aparición del valor buscado pero a veces necesitamos encontrar la dirección de la enésima celda por lo que será necesario crear una fórmula más avanzada que la anterior y eso es lo que haremos en la siguiente sección.
Encontrar la dirección de la enésima celda
Ya que no existe una función de Excel que nos permita buscar el número exacto de coincidencia que necesitamos, será necesario recurrir a las fórmulas matriciales. En lugar de mostrarte desde ahora la fórmula final, la iremos construyendo poco a poco.
La primera fórmula que te mostraré se encargará de hacer la comparación de cada una de las celdas con el valor buscado el cual se encuentra en la celda E1 y en caso de que exista una coincidencia devolverá el número de fila o de lo contrario devolverá una cadena vacía.
=SI(A1:A21=E1, FILA($A$1:$A$21), "")
Recuerda que esta es una fórmula matricial así que para probar su funcionamiento debes seleccionar el rango de celdas donde se desplegarán los resultados, ingresar la fórmula en la barra de fórmulas y pulsar Ctrl + Mayús + Entrar. Recuerda que algunas personas conocen a la tecla Mayús por su nombre en inglés: Shift.
Observa que solo las filas de la columna A con el nombre Luis son las filas que tienen un número bajo la columna G y el resto de las filas aparecen vacías. El siguiente paso es utilizar la función K.ESIMO.MENOR para obtener el número de aparición exacto que necesitamos:
=K.ESIMO.MENOR(SI(A1:A21=E1, FILA($A$1:$A$21), ""), E2)
El primer argumento de la función es nuestra fórmula anterior y el segundo argumento es la referencia a la celda E2 que es la que contiene el número de aparición que necesitamos. Insertaré esta fórmula en la celda E3 y pulsaré la combinación de teclas Ctrl + Mayús + Entrar.
Como resultado obtenemos el valor 7 que es precisamente el número de la fila de la segunda aparición del nombre Luis dentro de nuestro rango de búsqueda. Para obtener la dirección de la celda debemos pasar este resultado a la función DIRECCION de la siguiente manera:
=DIRECCION(K.ESIMO.MENOR(SI(A1:A21=E1, FILA($A$1:$A$21), ""), E2), 1)
Recuerda que esta es una función matricial y debemos pulsar Ctrl + Mayús + Entrar y como resultado obtendremos la dirección de la celda que contiene la segunda aparición del nombre Luis dentro del rango A1:A21.
Podrás hacer cualquier combinación de valores para obtener la dirección de la celda con el valor indicado en la celda E1 y el número de aparición de la celda E2. La fórmula devolverá el error #¡NUM! en caso de no encontrar ninguna coincidencia dentro del rango de búsqueda.
Dirección de la celda con el valor máximo
Hasta ahora hemos visto ejemplos con la columna que contiene cadenas de texto pero podemos lograr algunas combinaciones interesantes al trabajar con números. Por ejemplo, si necesito obtener la dirección de la celda con el valor máximo de la columna B, entonces puedo utilizar la siguiente fórmula:
=DIRECCION(COINCIDIR(MAX(B1:B21), B1:B21,0), 2)
Esta fórmula es muy similar a la primera fórmula del artículo donde utilizamos la función COINCIDIR pero en este caso la combinaremos con la función MAX para conocer el valor máximo del rango. Observa cómo la formula devuelve la dirección de la celda B19 que es la que contiene el valor más grande del rango:
Si en lugar del valor máximo queremos obtener la dirección de la celda que contiene el valor más pequeño, entonces solo debemos cambiar la función MAX por la función MIN de la siguiente manera:
=DIRECCION(COINCIDIR(MIN(B1:B21), B1:B21,0), 2)
Espero que los ejemplos que hemos desarrollado en este artículo sean de utilidad para esas ocasiones en las que necesitas encontrar la dirección de una celda a partir de un valor. Realiza estos ejemplos utilizando tus propios datos y pronto te familiarizarás con el uso de las funciones mostradas en el artículo.