Saber en qué rango cayó el valor encontrado, no solo su valor

Cuando no quieres el dato, quieres saber dónde está

Hay un momento en el trabajo con Excel en el que las funciones de búsqueda dejan de servirte, y no porque fallen. ÍNDICE y COINCIDIR hacen exactamente lo que prometen: te traen el valor de la fila que buscabas. El problema aparece cuando lo que necesitas no es el valor, sino la posición: en qué celda vive ese dato, para poder ir hasta allí, resaltarlo o encadenar otra referencia.

Es justo lo que planteó un miembro de la comunidad. Tenía una base de datos larga en dos columnas, buscaba un identificador y lo encontraba sin problema. Pero su pregunta era otra: quería que Excel le respondiera E9:F9, la dirección del rango donde había caído la fila localizada. Su primera intuición fue INDIRECTO, que es la función que todo el mundo asocia a "trabajar con referencias como texto". Y por ahí no salía.

Por qué INDIRECTO no era el camino

Merece la pena entender el desencuentro, porque es un error conceptual muy común. INDIRECTO va en la dirección contraria a la que hacía falta: recibe un texto que representa una referencia y devuelve el contenido de esa referencia. Es decir, convierte texto en dato.

Aquí el recorrido era el inverso: partiendo de una posición encontrada, había que construir el texto de la dirección. La función que hace eso es DIRECCION, y es una de esas piezas de Excel que mucha gente ha visto en una lista pero nunca ha tenido motivo para usar.

La solución que resolvió el caso

La propuesta que el autor adoptó combina tres funciones de una forma poco habitual:

`` =UNIRCADENAS(":";;DIRECCION(COINCIDIRX(I8;E:E);COLUMNA(E:F);4)) ``

La clave está en un detalle que no es evidente: DIRECCION acepta un vector en el argumento de columna. Al pasarle COLUMNA(E:F) no recibe un número, sino dos, así que no devuelve una dirección sino dos. Después, UNIRCADENAS las pega usando los dos puntos como separador, y el resultado es un rango completo en lugar de una celda suelta.

El cuarto argumento, el 4, es el que decide el tipo de referencia. Con ese valor la dirección sale relativa (E9:F9), que es la forma legible que se buscaba, en lugar de la versión con signos de dólar.

Convertir la dirección en un salto

Sobre esa misma base surgió una variante que va un paso más allá: envolver el resultado en HIPERVINCULO para tener un enlace que lleve directamente a la fila encontrada. Lo interesante no es el hiperenlace en sí, sino cómo se construye su destino.

Un hipervínculo interno necesita el nombre del libro. Escribirlo a mano funciona hasta el día en que alguien renombra el archivo y todos los enlaces se rompen. La alternativa es leerlo en tiempo de ejecución con CELDA("nombrearchivo") y quedarse con la parte final mediante TEXTODESPUES. Así la fórmula se adapta sola.

Esta variante conviene contarla con honestidad: al autor del caso no le llegó a funcionar el salto. Se probaron dos explicaciones, el separador de argumentos y la forma de pulsar el enlace, y ninguna lo resolvió. Nadie llegó a hacer el experimento que distinguiera entre las hipótesis, así que la causa quedó abierta. La técnica del destino auto-referenciado, en cambio, sí es aprovechable por su cuenta en cualquier hipervínculo interno.

Otros dos caminos

También apareció una vía sin DIRECCION, apoyada en DESREF para desplazarse hasta la fila encontrada y en CELDA para preguntarle su dirección. Devuelve una única celda en lugar del rango de dos columnas, así que encaja cuando basta con el punto de anclaje. Hay que tener presente que DESREF es volátil y se recalcula con cualquier cambio del libro.

Y la versión más directa de todas, que renuncia a la elegancia a cambio de legibilidad: localizar la fila una vez con LET y construir el texto concatenando las letras de columna a mano. Si la estructura no cambia, cumple perfectamente.

La versión que sobrevive a los cambios

La propuesta más generalizable trabaja sobre una tabla con nombre y evita cualquier referencia literal a columnas. Su pieza destacada es ELEGIRFILAS(FILA(_r1); COINCIDIRX(...)): en vez de calcular el número de fila sumando desplazamientos, toma el vector de números de fila del propio rango y elige la posición devuelta por la búsqueda. El resultado es una fórmula que sigue funcionando aunque muevas la tabla a otro sitio de la hoja.

Funciones clave

• DIRECCION: construye el texto de una referencia a partir de fila y columna. Admite vectores, que es lo que permite obtener un rango en lugar de una celda.
• COINCIDIRX: localiza la posición de un valor dentro de un rango, con más control que COINCIDIR sobre el modo de búsqueda.
• UNIRCADENAS: une varios textos con un separador común, ideal para pegar dos direcciones en un rango.
• ELEGIRFILAS: extrae filas concretas de una matriz por su posición, muy útil combinada con FILA para obtener números de fila reales.
• CELDA: devuelve información sobre una celda, incluida su dirección y el nombre del libro.

Conclusión

Un mismo problema, cinco maneras de resolverlo, y cada una enseña algo que se aplica muy lejos de este caso concreto: que DIRECCION trabaja con vectores, que el nombre del libro se puede leer sin escribirlo, y que sobre tablas estructuradas casi siempre existe una versión que no depende de dónde estén las cosas. Esa variedad es justamente lo que hace valiosos los hilos de la comunidad: rara vez hay una única respuesta correcta, y comparar enfoques enseña más que memorizar el que funciona.

Más casos con estas funciones

• LET
• UNIRCADENAS
• COINCIDIRX
• ELEGIRFILAS
• DESREF
• INDIRECTO

Más contenido de Excel en InflueXcel

• Multiplicar por coeficientes de escenario con INDICE, COINCIDIRX y SI.CONJUNTO Caso
  Un miembro de la comunidad tiene una tabla de conceptos con cantidades, precios y un campo "Escenario" (1, 2 o 3). Aparte, tiene una tabla d
• Buscar prefijos de longitud variable en otra columna: BYROW, MAP, REGEX y COINCIDIRX Caso
  Interesante problema planteado por un miembro: tiene una columna A con ~1.200 referencias de longitud variable y una columna C con ~276 text
• Eliminar celdas vacías al concatenar con DIVIDIRTEXTO y BYROW Caso
  Juan plantea lo que parece una duda sencilla: tiene filas con valores y celdas vacías intercaladas, y necesita concatenar solo los valores n
• ELEGIRFILAS con fechas de pago: ajustar el campo de fecha al repetir filas Caso
  Caso derivado de una duda previa sobre ELEGIRFILAS combinada con SECUENCIA para repetir una fila N veces. En esta ocasión Juan (@16623679309
• Por qué ELEGIRFILAS falla con índices vacíos o erróneos Caso
  Un miembro de la comunidad comparte un problema que a muchos nos ha dado algún dolor de cabeza: intenta usar SI.ND() para manejar errores en
• Estructurar correctamente LAMBDA con LET y parámetros opcionales Caso
  Nuevo reto de Excel resuelto por la comunidad: un miembro está creando una función LAMBDA personalizada para calcular potencia de bombeo (fó
• Funciones personalizadas en Excel: LET, LAMBDA y recursividad Tutorial
  Cómo pasar de una fórmula escrita a mano a una función propia que puedes llamar por su nombre en cualquier libro. Los tres vídeos de esta pá
• Filtrar una tabla por una lista de valores: una LAMBDA propia y su inversa Tutorial
  Te pasan una lista de 30 números de albarán y hay que sacar esas filas de una tabla de miles. Con el autofiltro es marcar casillas una a una
• BUSCARX con múltiples columnas: por qué no funciona y 4 alternativas Caso
  Juan plantea una duda que muchos han tenido alguna vez: quiere que BUSCARX le devuelva varias columnas a la vez (nombre y apellidos), pero l
• Extraer datos de archivos XML complejos: Power Query, VBA y Power Automate Caso
  Sergio plantea un problema real con datos abiertos del gobierno español: archivos XML de licitaciones públicas con más de 30 niveles de anid