La combinación de COINCIDIR, INDICE e INDIRECTO.

Nuevamente aplicaremos estas tres funciones, que por otro lado suelen ir siempre de la mano cuando hablamos de mostrar datos de base de datos. En esta ocasión trabajaremos sobre una base de datos de diferentes 'Tiendas', donde se cruzan 'Productos' en los diferentes 'Meses' del año; se trata de localizar algún valor que cruce con estas tras variables.
La estructura de la base de datos que nos encontramos es un poco peculiar, por lo que deberemos emplear, además de estas tres funciones COINCIDIR, INDICE e INDIRECTO, la función DIRECCION, celdas validadas y nombres a diferentes rangos de celdas. Todas son funciones o herramientas ya vistas en distintos momentos en este blog... pero de forma especial vamos a combinarlas.

Aunque el ejercicio planteado por un lector del blog es algo largo, por simplificar, supondremos que tenemos datos de 3 'Tiendas' y sólo 5 'Productos'.
Veamos cual es nuestra base de datos (VER FICHERO ADJUNTO):

haz click en la imagen

Los nombres asignados a los diferentes rangos han sido:

Eco =Datos!$AB$3:$AM$14
Metro =Datos!$D$3:$O$14
Wong =Datos!$P$3:$AA$14
 
meses =Hoja2!$C$3:$C$14
producto=Hoja2!$D$3:$D$7
Tiendas =Hoja2!$B$3:$B$5

Vemos como se ha definido un nombre para cada rango de celdas con datos de una 'Tienda' (Metro, Wong y Eco).
Lo que tenemos que calcular es, teniendo como datos el nombre de la tienda y el mes, la cantidad total vendida durante ese mes de todos los productos.

Como queremos algo dinámico crearemos un par de celdas validadas con los Meses (A2) y Tiendas (A3):

Sobre estas celdas generaremos nuestra fórmula que nos devolverá el dato requerido.
La primera parte de la fórmula será para indicarle a la función en que rango de meses (de qué tienda) ha de buscar; para ello construimos con la función DIRECCION un rango adaptable:
="Datos!"&DIRECCION(3;COLUMNA(INDIRECTO(A3)))&":"&DIRECCION(3;COLUMNA(INDIRECTO(A3))+11)
observemos cómo se ha empleado el valor de la celda validad A3, convenientemente convertida con la función INDIRECTO, para determinar el comienzo y fin del rango, combinando este rango con la función COLUMNA(ref) que nos devuelve el número de columna de la referencia indicada (si es un rango de celdas nos muestra la columna inicial); combinando todas las funciones obtenemos
'Datos!$D$3:$O$3' para la tienda Metro, o
'Datos!$P$3:$AA$3' para la tienda Wong, o
'Datos!$AB$3:$AM$3' para Eco.
Y todo de forma automática asociado a la celda validada A3.
Estos valores lo convertimos en algo entendible para Excel aplicándole nuevamente la función INDIRECTO:
INDIRECTO("Datos!"&DIRECCION(3;COLUMNA(INDIRECTO(A3)))&":"&DIRECCION(3;COLUMNA(INDIRECTO(A3))+11))

¿Y por qué tantas funciones anidadas?...por que necesitamos conocer en qué posición se encuentra el mes seleccionado con la otra de las celdas validada A2.
Esto lo conseguimos con la función COINCIDIR:
=COINCIDIR(A2;fórmula anterior;0)
ya sabemos qué número de columna del rango de la tienda seleccionada tiene el dato buscado.

Por último, seleccionaremos, con la función INDICE, el valor requerido; que se encontrará asociada indiscutiblemente al 'mes' y 'tienda' (en nuestro ejemplo, se encuentra en la fila 12):
=INDICE(INDIRECTO(A3);12;COINCIDIR(A2;INDIRECTO("Datos!"&DIRECCION(3;COLUMNA(INDIRECTO(A3)))&":"&DIRECCION(3;COLUMNA(INDIRECTO(A3))+11));0))

Adjunto fichero para mejor análisis.

INDIRECTO INDICE C...

Hosted by eSnips