BUSCARV sobre varios origenes

Muchas veces a lo largo de mis años como consultor y formador me han plantado la misma cuestión. (¿es posible hacer un BUSCARV sobre diferentes rangos?)... y en la entrada anterior analizamos una manera de recuperar un valor buscado que podría encontrarse en diferentes tablas... con un par de inconvenientes:
1-se necesitaba una acción manual de refresco de la consulta de Power Query,
2-estábamos restringidos por el número de tablas.

Hoy veremos un trabajo similar sin ninguna de las limitaciones anteriores... (solo estaremos sujetos a la que nos marca el uso de fórmulas matriciales).
Así pues empecemos a construir un solo BUSCARV sobre infinitos orígenes.

Partimos en este caso de tres tablas (pero podrían ser las que necesitáramos):

Necesitamos tener una lista con los nombres de las diferentes tablas, en mi caso los he dispuesto en el rango B13:B15:
Tbl_135
Tbl_246
Tbl_78910

A cuyo rango además he asignado el nombre definido: Lista.

Hasta este punto tenemos pues dos nombres definidos:
lista =BUSCARV_Multiple!$B$13:$B$15
vBuscado =BUSCARV_Multiple!$B$8

Estamos preparados... en la celda C8 introducimos la fórmula matricial buscada:
=BUSCARV(vBuscado;INDIRECTO(INDICE(lista;SUMA((CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0)*FILA(INDIRECTO("1:"&CONTARA(lista))));1));3;0)

(recuerda presionar Ctrl+Mayusc+Enter para validar en lugar de solo Enter).
El resultado aparece mágicamente.. dará igual en qué tabla se encuentre, si existe en alguna de ellas, obtendremos el valor deseado!.

la explicación.
La clave del asunto es el uso de la función:
CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0
que devuelve una matriz de VERDADEROS y FALSO, una por cada tabla... y tendremos un VERDADERO solo para la tabla donde se encuentre el valor buscado, por ejemplo, para el valor buscado 'p7' tendríamos la matriz:
{FALSO;FALSO;VERDADERO}
es decir, es cierto que 'p7' la encontramos en la tercera tabla...

Para convertir esa matriz {FALSO;FALSO;VERDADERO} en un tres (tercera tabla) lo multiplicamos por una matriz de constantes {1;2;3}
lo que conseguimos con la clásica fórmula
FILA(INDIRECTO("1:"&CONTARA(lista)))
que nos devolverá la matriz de naturales desde 1 hasta el número de tablas existente.

El producto elemento a elemento y su suma final nos retornará el número buscado: 3 (para este ejemplo).

Si aplicamos sobre nuestra 'lista' de Tablas la función INDICE, nuestro tres devuelve el nombre de la Tabla deseada... si sobre ella aplicamos la función INDIRECTO ya podremos trabajar sobre esa tabla igual que si la seleccionáramos.

Si ya tenemos la tabla, con la fórmula comentada:
INDIRECTO(INDICE(lista;SUMA((CONTAR.SI(INDIRECTO(lista&"[Id]");vBuscado)>0)*FILA(INDIRECTO("1:"&CONTARA(lista))));1))

estamos en disposición de aplicar un BUSCARV como haríamos normalmente:
=BUSCARV(vBuscado; tabla_recuperada_con_nuestra_fórmula;3;0)

Objetivo logrado!.