Búsqueda de elementos idénticos en distintas tablas

Es muy habitual que la información con la que vamos a trabajar se encuentre dispersa en distintas hojas de un libro o incluso en distintos libros (archivos). Sin entrar en la discusión de cómo se debe plantear correctamente nuestro modelos en Excel para sacarle el mayor rendimiento posible con el mínimo esfuerzo (artículo/s que prometo abordar en este blog) me planteáis la siguiente consulta:

“Dentro de un archivo tengo los nombres de 32 comerciales y 10 hojas distintas con información relativa a sus salarios, ventas, comisiones, etcétera. Cuando deseo realizar un resumen que incluya toda esta información acabo copiando y pegando a mano ¿Hay alguna manera de que, por ejemplo, busquemos los nombres de los comerciales y nos devuelva toda la información relativa a éstos que se encuentre en las distintas hojas?”. 

En este caso que me proponéis, la solución es bastante sencilla, ya que la información sigue un mismo patrón aunque se encuentre dispersa en distintas hojas. A saber: todas las tablas tienen dos columnas, siendo la primera el nombre de los comerciales y dónde la segunda contiene la información sobre ventas, comisiones, etcétera. En nuestro ejemplo vamos a plantear el caso para 10 comerciales y 3 tablas repartidas en 3 hojas distintas: 

1. En la hoja Salarios tenemos nuestra tabla en el rango A6:B16 y lo primero que tenemos que hacer es, precisamente darle nombre al rango. Para ello seleccionamos A7:B16 (no incluimos los nombres de campo), hacemos clic en el cuadro de nombres ( a la izquierda de la barra de fórmulas) y escribimos salarios:

2. En la hoja Ventas tenemos nuestra tabla en el rango A2:B12 y hacemos lo mismo que en el caso anterior: seleccionamos A3:B12, hacemos clic en el cuadro de nombres y  escribimos ventas.

3. En la hoja Comisiones tenemos nuestra tabla en el rango A13:B23 y hacemos lo mismo que en el caso anterior: seleccionamos A14:B23, hacemos clic en el cuadro de nombres y  escribimos comisiones.

4. Nos situamos en la hoja Resumen y preparamos el cuadro (es imprescindible que los nombres de campo (rango B3:D3) sean IDÉNTICOS a los nombres que hemos creado para los distintos rangos):

5. Nos situamos en la celda B4 y escribimos la siguiente fórmula:

=BUSCARV($A4;INDIRECTO(B$3);2;FALSO)

Fíjese que en el segundo argumento de la función BUSCARV (matriz_buscar_en) hemos anidado la función INDIRECTO. El objetivo es aprovechar el nombre del campo, por ejemplo salarios, para que BUSCARV localice en dicha tabla la información deseada. Cuando copiemos la fórmula de B4 hacia la derecha la matriz_buscar_en irá variando con el nombre del campo. Para ello hemos utilizado referencias mixtas fijando la columna para los nombres de los comerciales ($A4) y fijando la fila para los nombres de las tablas (B$3). De esta manera podremos copiar hacia la derecha y hacia abajo la fórmula de B4 y con una única fórmula habremos terminado nuestro resumen.