De una dirección escrita en texto al bloque de celdas vecinas: por qué MAP con INDIRECTO da #¡CALC!

Juan tenía una columna con direcciones de celda escritas como texto (CC11, CC29, CC31…) y quería traer, para cada una, las cuatro celdas de su derecha: de CD11 a CG11, de CD29 a CG29, etc. Su primer intento fue montar los rangos como texto ("CC11:CG11") y pasarlos por INDIRECTO dentro de MAP:

``
=MAP(IG15#;LAMBDA(x;INDIRECTO(x)))
`

Una a una, INDIRECTO("CC11:CG11") funciona. Dentro de MAP, da #¡CALC!.

Por qué. MAP recorre la matriz celda a celda y cada vuelta tiene que devolver un solo valor. Aquí cada vuelta devuelve un rango de cinco celdas, y MAP no sabe colocar una matriz dentro de otra. No es un fallo de INDIRECTO: es la misma limitación que tienen BYROW y BYCOL.

La comunidad respondió con tres enfoques.

---

1. Una referencia por celda (Nacho). En vez de pedirle a MAP rangos enteros, se construye la dirección de cada celda cruzando una fila con las letras de las columnas (C2:F2 = CC, CD, CE, CF) y una columna con los números de fila (B3:B5 = 11, 29, 31). El & expande la fila contra la columna y sale una rejilla de direcciones sueltas; ahora sí, MAP aplica INDIRECTO a cada una y cada vuelta devuelve un único valor:

`
=MAP(C2:F2&B3:B5;INDIRECTO)
`

Fíjate en que INDIRECTO va sin LAMBDA: cuando la función solo tiene un argumento obligatorio, basta con su nombre (lo que se conoce como eta-lambda). Juan lo probó: «funciona perfecto».

2. Sin INDIRECTO (Rommel Alemán). Rommel prefiere no usar INDIRECTO (es volátil y se recalcula con cualquier cambio del libro) y propuso sacar el valor con INDICE, buscando la columna con COINCIDIR en la fila 1:

`
=MAP(C2:F2;B3:B5;LAMBDA(col;fila;INDICE(A:XFD;fila;COINCIDIR(col;1:1;0))))
`

Al probarla para este caso, tal cual devuelve #N/D en todas las celdas menos la primera: a MAP le llegan una fila de 4 y una columna de 3, y MAP no las expande como hace el &; empareja posición a posición y lo que no tiene pareja sale #N/D. La idea es buena y el arreglo es quitar el MAP, porque INDICE ya acepta una matriz de filas y otra de columnas y devuelve la rejilla entera:

`
=INDICE(A:XFD;B3:B5;COINCIDIR(C2:F2;1:1;0))
`

Esto exige que la fila 1 de la hoja tenga la letra de cada columna. Si no la tiene, el número de columna se puede sacar de las propias letras:

`
=INDICE(A:XFD;B3:B5;COINCIDIR(C2:F2;SUSTITUIR(DIRECCION(1;SECUENCIA(1;16384);4);"1";"");0))
`

3. Generar el texto de las direcciones (Gerson Pineda). Lo que Juan había pintado en rojo en su captura eran, literalmente, los textos CD11, CE11, CF11, CG11… Gerson los fabrica a partir de la lista de direcciones (A1:A6; la fórmula, pasada aquí a punto y coma):

`
=LET(
r; A1:A6;
IZQUIERDA(DIRECCION(1; 81 + {1\2\3\4}; 4); 2) & SI(SECUENCIA(FILAS(r); 4); TEXTODESPUES(r; "CC"))
)
`

DIRECCION(1;82;4) devuelve "CD1" y IZQUIERDA(...;2) se queda con "CD"; TEXTODESPUES(r;"CC") saca el número de fila, y el SI(SECUENCIA(...)) lo repite en las cuatro columnas. Si lo que quieres son los valores y no los textos, basta con ponerle encima MAP e INDIRECTO: con la fórmula anterior en B1, =MAP(B1#;INDIRECTO).

Dos límites que conviene saber: el 81 y el "CC" están escritos a mano para este caso, e IZQUIERDA(...;2) solo acierta en columnas de dos letras. Probada con direcciones de la columna X, devuelve X111 en lugar de Y11.

---

El fichero adjunto trae datos de ejemplo y los tres enfoques funcionando, cada uno en su bloque (las celdas no son las mismas que en el texto), incluido el #¡CALC!` del planteamiento para verlo fallar.

Tienes una lista de direcciones de celda escritas como texto y quieres traer lo que hay a su lado. Parece un trabajo perfecto para MAP con INDIRECTO, lo escribes y Excel te devuelve #¡CALC!. Este caso salió en la comunidad de InflueXcel y explica por qué pasa y tres formas de resolverlo.

El problema

Juan tenía en una columna direcciones como CC11, CC29 o CC31, y para cada una quería las cuatro celdas de su derecha: de CD11 a CG11, de CD29 a CG29, y así con toda la lista. Montó los rangos como texto ("CC11:CG11") y los pasó por INDIRECTO dentro de MAP:

=MAP(IG15#;LAMBDA(x;INDIRECTO(x)))

Con una sola dirección, INDIRECTO("CC11:CG11") funciona sin problema. Dentro de MAP, no.

Por qué MAP devuelve #¡CALC!

MAP recorre una matriz celda a celda, y en cada vuelta espera un solo valor. Si en una vuelta la LAMBDA devuelve un rango de cinco celdas, MAP tendría que meter una matriz dentro de otra, y eso Excel no lo sabe hacer: responde con #¡CALC!. La culpa no es de INDIRECTO. BYROW y BYCOL tienen la misma limitación.

La clave está en cambiar la pregunta. En lugar de pedir un bloque por vuelta, hay que conseguir que cada vuelta pida una celda.

Solución paso a paso

1. Una dirección por celda con MAP e INDIRECTO

La propuesta de Nacho: poner en una fila las letras de las columnas (C2:F2, con CC, CD, CE y CF) y en una columna los números de fila (B3:B5, con 11, 29 y 31). Al unirlas con &, Excel expande la fila contra la columna y sale una rejilla con una dirección en cada celda (CC11, CD11…). Ahora cada vuelta de MAP recibe una dirección suelta y devuelve un único valor:

=MAP(C2:F2&B3:B5;INDIRECTO)

INDIRECTO va sin LAMBDA. Cuando una función tiene un solo argumento obligatorio, basta con escribir su nombre como segundo argumento de MAP, BYROW o BYCOL: es lo que se conoce como eta-lambda. Juan lo probó y confirmó que funcionaba.

2. Sin INDIRECTO: INDICE con filas y columnas

Rommel Alemán prefiere evitar INDIRECTO, que es una función volátil: se recalcula con cualquier cambio del libro y, en hojas grandes, se nota. Su idea es sacar cada valor con INDICE, localizando la columna con COINCIDIR. La propuso dentro de MAP con dos matrices, una fila y una columna, y así devuelve #N/D en casi todas las celdas: MAP empareja los elementos posición a posición y no expande una fila contra una columna como hace el &.

El arreglo es quitar MAP. INDICE ya admite una matriz de números de fila y otra de columnas, y devuelve la rejilla completa:

=INDICE(A:XFD;B3:B5;COINCIDIR(C2:F2;1:1;0))

Esta versión busca las letras en la fila 1, así que necesita que esa fila tenga los encabezados. Si no los tiene, el número de columna se saca de las propias letras comparándolas con la lista de todas las columnas de la hoja:

=INDICE(A:XFD;B3:B5;COINCIDIR(C2:F2;SUSTITUIR(DIRECCION(1;SECUENCIA(1;16384);4);"1";"");0))

3. Generar el texto de las direcciones vecinas

Gerson Pineda se fijó en otra cosa: lo que Juan había marcado en su captura eran, literalmente, los textos CD11, CE11, CF11 y CG11. Su fórmula los fabrica a partir de la lista:

=LET(
    r; A1:A6;
    IZQUIERDA(DIRECCION(1; 81 + {1\2\3\4}; 4); 2) & SI(SECUENCIA(FILAS(r); 4); TEXTODESPUES(r; "CC"))
)

DIRECCION con el tipo 4 devuelve una dirección relativa ("CD1" para la columna 82) e IZQUIERDA se queda con la letra. TEXTODESPUES saca el número de fila de cada dirección, y SI(SECUENCIA(...)) lo repite en las cuatro columnas. Si después quieres los valores, basta con MAP e INDIRECTO sobre el resultado.

Tiene dos límites: el 81 y el "CC" están fijos para este caso, e IZQUIERDA(...;2) solo acierta con columnas de dos letras. Con direcciones de la columna X, por ejemplo, el resultado sale mal.

Funciones clave

  • MAP: aplica una función a cada elemento de una matriz. Cada vuelta tiene que devolver un único valor.
  • INDIRECTO: convierte un texto en una referencia. Es volátil.
  • INDICE: devuelve el valor de una fila y columna dadas, y acepta matrices de filas y de columnas a la vez.
  • COINCIDIR: da la posición de un valor dentro de una fila o columna.
  • DIRECCION: construye una dirección de celda como texto a partir de la fila y la columna.
  • TEXTODESPUES: extrae lo que va detrás de un delimitador.

Conclusión

El #¡CALC! de MAP con rangos tiene arreglo: se cambia la pregunta para que cada vuelta devuelva un solo valor, o se prescinde de MAP cuando otra función ya sabe devolver la rejilla entera. Este caso salió del grupo de WhatsApp de InflueXcel, gracias a la duda de Juan y a las respuestas de Nacho, Rommel Alemán y Gerson Pineda. El fichero de la ficha trae los tres enfoques funcionando.

Más casos con estas funciones

Más contenido de Excel en InflueXcel