Excel: cambiar datos por semana con lista desplegable (XLOOKUP, BUSCARX, INDEX, INDIRECTO)

¿Quieres que un bloque de celdas muestre valores distintos según la semana elegida en un desplegable y que el patrón se repita cada ocho semanas? Aquí tienes tres soluciones fiables en Excel —con fórmulas listas para copiar, estructura de datos sugerida, trucos de validación y variantes para equipos con separador de lista en coma o punto y coma— para que lo consigas de forma robusta y mantenible.

Índice

Qué vas a lograr

Configurarás una hoja “principal” con un selector de semana (Week 1…Week 8) que alimenta automáticamente grupos de celdas —por ejemplo, las situadas bajo los encabezados Reward 1 y Reward 2— en posiciones como columnas O/P/S/T y filas 4, 8, 12, etc. Los valores se recuperan desde una hoja de datos y el calendario se recicla cada ciclo de ocho semanas.

Estructura base recomendada

Para evitar referencias frágiles y facilitar el copiado masivo de fórmulas, te sugiero esta organización mínima:

Hoja de datos llamada Data con una única tabla que contenga todas las combinaciones de semana y recompensa por instalación (Facility).
Hoja principal con:
- Una celda con lista desplegable para elegir la semana (por ejemplo M2).
- Encabezados de las recompensas (por ejemplo O5 = Reward 1 y S5 = Reward 2).
- Una columna con el nombre de la instalación para cada panel (por ejemplo N4 y R4 para los paneles bajo Reward 1 y Reward 2 respectivamente).

Opción A recomendada: tabla única de datos y doble XLOOKUP

Es la alternativa más limpia cuando tu hoja principal se organiza por filas de Facilities y columnas de “semana + recompensa”. Requiere Excel 365 o Excel 2021.

Estructura sugerida de la hoja de datos

Crea una hoja llamada Data con esta cuadrícula:

Facility	Week 1 Reward 1	Week 1 Reward 2	Week 2 Reward 1	Week 2 Reward 2	Week 3 Reward 1	Week 3 Reward 2	Week 4 Reward 1	Week 4 Reward 2	Week 5 Reward 1	Week 5 Reward 2	Week 6 Reward 1	Week 6 Reward 2	Week 7 Reward 1	Week 7 Reward 2	Week 8 Reward 1	Week 8 Reward 2
Facility A	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…
Facility B	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…	…
…	Completa con tus valores reales

En términos de rangos (ajústalos a tu tamaño real):

Data!$A$2:$A$11: lista de Facilities.
Data!$B$1:$Q$1: fila de encabezados combinados “Week n Reward m”.
Data!$B$2:$Q$11: matriz de valores.

Preparación de la hoja principal

En M2 crea una Validación de datos de tipo Lista con los elementos Week 1 a Week 8. Lo más práctico es crear una pequeña lista auxiliar con ="Week "&SECUENCIA(8) y usar ese rango como origen.
Asegúrate de que O5 y S5 contienen exactamente Reward 1 y Reward 2 (sin espacios extra).
En la columna N coloca el nombre de la Facility correspondiente a cada fila de datos del panel de Reward 1. En la columna R haz lo mismo para el panel de Reward 2.

Fórmulas listas para copiar

Panel bajo Reward 1 (pon en O6 y copia a las demás celdas objetivo del panel):

=IFERROR(
  XLOOKUP($N4, Data!$A$2:$A$11,
    XLOOKUP($M$2 &amp; " " &amp; O5, Data!$B$1:$Q$1, Data!$B$2:$Q$11)
  ),
0)

Panel bajo Reward 2 (pon en S6 y copia a las demás celdas objetivo del panel):

=IFERROR(
  XLOOKUP($R4, Data!$A$2:$A$11,
    XLOOKUP($M$2 &amp; " " &amp; S5, Data!$B$1:$Q$1, Data!$B$2:$Q$11)
  ),
0)

Cómo funciona: la búsqueda interna (XLOOKUP) identifica la columna correcta en la hoja Data construyendo el texto del encabezado a partir de la semana seleccionada en M2 y el título de la recompensa (O5 o S5). La búsqueda externa localiza la fila por Facility y devuelve el valor de esa columna. El IFERROR devuelve 0 si hay desajustes.

Si tu Excel usa punto y coma como separador de argumentos (configuración regional habitual en español), reemplaza las comas por punto y coma:

=SI.ERROR(
  BUSCARX($N4; Data!$A$2:$A$11;
    BUSCARX($M$2 &amp; " " &amp; O5; Data!$B$1:$Q$1; Data!$B$2:$Q$11)
  );
0)

Versión con tablas estructuradas

Si conviertes tu rango de Data en una tabla de Excel (Ctrl+T) llamada tblData y tu lista de Facilities está en otra tabla tblMain, puedes ganar legibilidad:

=IFERROR(
  XLOOKUP([@Facility],
    tblData[Facility],
    XLOOKUP($M$2 &amp; " " &amp; O$5, tblData[#Headers], tblData[[#Data] [Week 1 Reward 1]:[Week 8 Reward 2]])
  ),
0)

En locales españoles:

=SI.ERROR(
  BUSCARX([@Facility];
    tblData[Facility];
    BUSCARX($M$2 &amp; " " &amp; O$5; tblData[#Encabezados]; tblData[[#Datos] [Week 1 Reward 1]:[Week 8 Reward 2]])
  );
0)

Ventajas clave de esta opción

Mantienes una sola fuente de la verdad para todas las semanas y recompensas.
Los encabezados son auto-documentados y la fórmula se adapta automáticamente si reordenas las columnas.
Los errores se gestionan elegantemente, evitando #N/A visibles.

Opción B práctica: una tabla por semanas en columnas con XLOOKUP simple

Útil si únicamente cambia la columna según la semana elegida y te interesa obtener toda la columna de resultados de una vez (derrama). Esta variante es ideal para reportes rápidos.

Estructura típica:

Encabezados en A1:H1: Week 1…Week 8.
Datos en A2:Hn (cada columna corresponde a una semana).
Desplegable en F1 con los mismos textos de los encabezados.

Fórmula bajo el desplegable (se derrama hacia abajo):

=XLOOKUP($F$1, $A$1:$H$1, $A$2:$H$100)

Versión con punto y coma:

=BUSCARX($F$1; $A$1:$H$1; $A$2:$H$100)

Ventaja: simplicidad y lectura clara; limitación: menos flexible si agregas más atributos (por ejemplo, varias recompensas por semana).

Opción C clásica: rangos por semana con INDEX e INDIRECT

Escoge esta alternativa si no tienes XLOOKUP o prefieres dividir los datos por nombre de rango o por hoja. Funciona bien, pero INDIRECT es volátil y puede penalizar el rendimiento en libros grandes.

Crea y nombra rangos de igual tamaño: Week1, Week2, …, Week8 (cada uno cubre la matriz de esa semana).
En una celda de entrada (por ejemplo A1) pon la lista desplegable con esos nombres exactos.
En la esquina superior izquierda de tu salida (por ejemplo B2) escribe:

=INDEX(INDIRECT($A$1), ROW()-ROW($B$2)+1, COLUMN()-COLUMN($B$2)+1)

Versión con funciones en español y punto y coma:

=INDICE(INDIRECTO($A$1); FILA()-FILA($B$2)+1; COLUMNA()-COLUMNA($B$2)+1)

Después, copia la fórmula por todo el rango de salida. Si cambias el valor del desplegable en A1, el bloque mostrará la tabla de esa semana.

Consejo: si te preocupa el rendimiento, evita esta opción en libros muy grandes o con muchos cálculos dependientes.

Derivar la semana automáticamente desde una fecha con ciclo de ocho semanas

Si en vez de elegir manualmente la semana quieres calcularla a partir de una fecha (por ejemplo la fecha de una visita o de un evento), emplea un punto de inicio y una fórmula con MOD para ciclar cada ocho semanas. Suponiendo:

A2 contiene la fecha base.
inicio es una celda con la fecha que define el arranque de la semana uno.

Fórmula genérica:

="Week " &amp; MOD(INT( (A2 - inicio) / 7 ), 8) + 1

En español con punto y coma:

="Week " &amp; MOD(INT((A2 - inicio) / 7); 8) + 1

Esto devuelve el texto Week 1 a Week 8 que puedes usar directamente como origen del desplegable o como entrada para las fórmulas de la Opción A. Si tus semanas empiezan un día distinto al de inicio, ajusta inicio al lunes (o el día que definas) del primer ciclo.

Generar la lista del desplegable de forma dinámica

Para no escribir a mano los ocho elementos, crea ocho celdas seguidas con:

="Week " &amp; SEQUENCE(8)

En español:

="Week " &amp; SECUENCIA(8)

Selecciona ese rango como origen de la validación de datos. Si necesitas la lista en una sola celda separada por comas (para documentar o depurar), usa:

=TEXTJOIN(", ", TRUE, "Week " &amp; SEQUENCE(8))

En español:

=UNIRTEXTO(", "; VERDADERO; "Week " &amp; SECUENCIA(8))

Variantes y mejoras útiles

Uso de LET para simplificar y acelerar

LET te permite definir variables y evitar repetir rangos. Aplicado a la Opción A:

=LET(
  facility; $N4;
  semana;   $M$2;
  premio;   O5;
  filas;    Data!$A$2:$A$11;
  heads;    Data!$B$1:$Q$1;
  matriz;   Data!$B$2:$Q$11;
  IFERROR(
    XLOOKUP(facility; filas; XLOOKUP(semana &amp; " " &amp; premio; heads; matriz));
  0))

Garantizar coincidencias exactas con datos limpios

Si sospechas de espacios sobrantes o caracteres invisibles, envuelve los términos con TRIM/ESPACIOS y CLEAN/LIMPIAR:

=IFERROR(
  XLOOKUP(CLEAN(TRIM($N4)), CLEAN(TRIM(Data!$A$2:$A$11)),
    XLOOKUP(CLEAN(TRIM($M$2 &amp; " " &amp; O5)), CLEAN(TRIM(Data!$B$1:$Q$1)), Data!$B$2:$Q$11)
  ), 0)

Versión en español:

=SI.ERROR(
  BUSCARX(LIMPIAR(ESPACIOS($N4)); LIMPIAR(ESPACIOS(Data!$A$2:$A$11));
    BUSCARX(LIMPIAR(ESPACIOS($M$2 &amp; " " &amp; O5)); LIMPIAR(ESPACIOS(Data!$B$1:$Q$1)); Data!$B$2:$Q$11)
  ); 0)

Selección de columnas con XMATCH y CHOOSECOLS

Si prefieres separar la localización de la columna del retorno, puedes usar XMATCH/COINCIDIRX con CHOOSECOLS/ELEGIRCOLS:

=LET(
  heads, Data!$B$1:$Q$1,
  data,  Data!$B$2:$Q$11,
  target, $M$2 &amp; " " &amp; O5,
  col, XMATCH(target, heads),
  IFERROR(XLOOKUP($N4, Data!$A$2:$A$11, CHOOSECOLS(data, col)), 0)
)

En español:

=LET(
  heads; Data!$B$1:$Q$1;
  data;  Data!$B$2:$Q$11;
  target; $M$2 &amp; " " &amp; O5;
  col; COINCIDIRX(target; heads);
  SI.ERROR(BUSCARX($N4; Data!$A$2:$A$11; ELEGIRCOLS(data; col)); 0)
)

Validación de datos con mensajes de entrada y error

Refuerza la calidad de la entrada añadiendo:

Mensaje de entrada: explica que solo se admiten Week 1…Week 8.
Alerta de error: evita que el usuario escriba un texto que no coincida exactamente con los encabezados de Data.

Mapa de celdas y disposición de paneles

Si tu diseño usa dos paneles paralelos con bloques que se repiten cada cuatro filas, este esquema te puede servir como guía:

Celda	Propósito	Ejemplo de contenido
M2	Desplegable de semana	Week 1…Week 8
O5	Encabezado de panel izquierdo	Reward 1
S5	Encabezado de panel derecho	Reward 2
N4, N8, N12…	Facility vinculada a celdas del panel izquierdo	Facility A, B, …
R4, R8, R12…	Facility vinculada a celdas del panel derecho	Facility A, B, …
O6, O10, O14…	Fórmula de panel izquierdo	Usa la fórmula de XLOOKUP anidada
S6, S10, S14…	Fórmula de panel derecho	Usa la fórmula de XLOOKUP anidada

Para replicar el patrón, copia las fórmulas del primer bloque y pégalas en los bloques siguientes cuidando de que las celdas con los nombres de Facility (Nx y Rx) apunten a la fila adecuada.

Consejos prácticos y errores comunes

Nombres exactos: escribe los Facilities exactamente igual en la hoja Data y en la hoja principal. No insertes espacios para “alinear” visualmente; usa la alineación de celda.
Rangos reales: ajusta A2:A11, B1:Q1, B2:Q11, etc. a tus dimensiones. Dejar filas o columnas vacías dentro del rango puede producir #N/A inesperados.
Valor por defecto: con IFERROR(...,0)/SI.ERROR(...;0) evitas errores visibles y tienes control del valor por defecto (0, cadena vacía o “No encontrado”).
Compatibilidad: XLOOKUP/BUSCARX está disponible en Excel 365/2021. En versiones anteriores recurre a INDEX+MATCH o a la Opción C.
Localización: además del separador de argumentos, los nombres de función cambian: IFERROR→SI.ERROR, INDEX→INDICE, INDIRECT→INDIRECTO, ROW→FILA, COLUMN→COLUMNA, TEXTJOIN→UNIRTEXTO, SEQUENCE→SECUENCIA, XMATCH→COINCIDIRX, CHOOSECOLS→ELEGIRCOLS.
Estabilidad: evita INDIRECT salvo que lo necesites; al ser volátil recalcula con cualquier cambio.
Encabezados como clave: la robustez de la Opción A depende de que los encabezados de Data coincidan exactamente con Week n Reward m. Si prefieres español, cambia también los textos que concatenas en la fórmula.

Compatibilidad con versiones anteriores sin XLOOKUP

Recrea la Opción A con INDEX y MATCH:

=IFERROR(
  INDEX(Data!$B$2:$Q$11,
        MATCH($N4, Data!$A$2:$A$11, 0),
        MATCH($M$2 &amp; " " &amp; O5, Data!$B$1:$Q$1, 0)
  ),
0)

En español:

=SI.ERROR(
  INDICE(Data!$B$2:$Q$11;
        COINCIDIR($N4; Data!$A$2:$A$11; 0);
        COINCIDIR($M$2 &amp; " " &amp; O5; Data!$B$1:$Q$1; 0)
  );
0)

Ventaja: funciona en Excel 2010–2019. Desventaja: menos intuitivo y más propenso a errores al cambiar el orden de columnas.

Ejemplo completo que puedes replicar

Supón que en Data tienes tres instalaciones y valores ficticios. Monta la tabla de encabezados tal cual se mostró y rellena algunos números. En la hoja principal:

M2: desplegable con Week 1…Week 8 (o con la fórmula de SECUENCIA y rango derramado).
O5 = Reward 1, S5 = Reward 2.
N4:N11 y R4:R11 con los nombres de Facilities en el orden que desees.
O6 y S6: pega las fórmulas de la Opción A y copia por los bloques necesarios (por ejemplo hasta fila 30 o más).

Al cambiar el valor de M2, verás que ambos paneles se actualizan de inmediato mostrando los números de la semana elegida. Cambiar el texto del encabezado a Reward 1/Reward 2 de manera distinta a la hoja Data provocará que la fórmula no encuentre coincidencias; por eso conviene fijar esos encabezados y proteger la hoja si otros usuarios editan.

Estilo y mantenimiento del libro

Nombrar correctamente: usa nombres de tablas (tblData, tblMain) y rangos con sentido.
Validación proactiva: impide la entrada libre en M2 marcando Rechazar entrada en la alerta de error.
Formato: aplica formato de número a las celdas objetivo (Generales, Moneda, etc.) para evitar que un cero por defecto parezca un dato válido.
Documentación ligera: agrega un comentario en M2 con una nota del origen de datos y la ruta de la hoja Data.
Pruebas: construye un caso mínimo con valores únicos por semana para detectar desajustes de encabezados al instante.

Solución de problemas frecuentes

La fórmula devuelve cero en todas las celdas
Comprueba que IFERROR está ocultando un #N/A. Reemplaza temporalmente IFERROR(...,0) por la expresión interna para ver el error real. Verifica que $M$2 & " " & O5 coincide con un encabezado de Data y que los nombres de Facility son idénticos.

Resultados en filas “desplazados”
Asegúrate de que las celdas de referencia a Facility ($N4, $R4) están ancladas correctamente respecto a la celda con fórmula. Si copias la fórmula a otra región, revisa si debes convertir $N4 en N$4 o mantenerlo como $N4 según la dirección del copiado.

Encabezados en español o formatos mixtos
Si los encabezados de Data están en español (Semana 1 Recompensa 1), cambia la concatenación de la fórmula para que produzca exactamente esa cadena.

Necesito ocultar errores en vez de cero
Sustituye el último argumento por comillas vacías "" o por un texto como "No encontrado".

Quiero incluir más recompensas
Amplía la tabla Data con nuevas columnas (Reward 3, etc.) y replica el patrón de los encabezados. La Opción A seguirá funcionando porque busca por el texto completo del encabezado.

Resumen operativo

Para cambiar datos por semana mediante desplegable en ciclos repetitivos, la configuración más sólida es una tabla única de datos y una búsqueda doble por encabezado y Facility con XLOOKUP/BUSCARX. Si tu escenario es simple, la variante de columnas por semana con BUSCARX directo rinde muy bien. En entornos sin las funciones modernas, INDICE+COINCIDIR o INDIRECTO te sacan del paso, con la advertencia de rendimiento habitual. Completa la solución con validación de datos, nombres exactos y limpieza de textos para que sea resistente a errores y fácil de mantener.

Fórmulas esenciales, compactas

Panel izquierdo:

=IFERROR(
  XLOOKUP($N4, Data!$A$2:$A$11,
    XLOOKUP($M$2 & " " & O5, Data!$B$1:$Q$1, Data!$B$2:$Q$11)
  ),
0)

Panel derecho:

=IFERROR(
  XLOOKUP($R4, Data!$A$2:$A$11,
    XLOOKUP($M$2 & " " & S5, Data!$B$1:$Q$1, Data!$B$2:$Q$11)
  ),
0)

Semana desde fecha, ciclo de ocho:

="Week " & MOD(INT((A2 - inicio)/7), 8) + 1

Guía rápida de localización

Inglés	Español	Notas
XLOOKUP	BUSCARX	Disponible en 365/2021
IFERROR	SI.ERROR	Control de error
INDEX	INDICE	Compatibilidad amplia
MATCH	COINCIDIR	Índice de columna/fila
INDIRECT	INDIRECTO	Volátil
ROW	FILA	Índice de fila
COLUMN	COLUMNA	Índice de columna
TEXTJOIN	UNIRTEXTO	Lista unida
SEQUENCE	SECUENCIA	Lista 1…n
XMATCH	COINCIDIRX	Búsqueda flexible
CHOOSECOLS	ELEGIRCOLS	Selecciona columnas
TRIM	ESPACIOS	Limpia espacios
CLEAN	LIMPIAR	Quita caracteres no imprimibles

Con estas tres rutas —tabla única con doble búsqueda, columnas por semana o rangos por semana— podrás adaptar tu libro a prácticamente cualquier diseño existente, desde reportes compactos hasta cuadros de mando que se repiten por bloques. Lo importante es que tus encabezados sean estables, tus rangos estén bien definidos y el texto del desplegable coincida exactamente con los encabezados de origen.