Cómo obtener coincidencias de una tabla cruzada.

Una consulta que me llegó hace algunos días preguntaba sobre la forma en que se podría obtener listados condicionados o filtrados sobre una tabla cruzada:

...DISPONGO DE UNA TABLA CON VARIAS COLUMNAS (45) Y FILAS (266). CADA FILA SE REFIERE A UNA CASA DIFERENTE Y CADA COLUMNA A UN DETERMINADO SERVICIO. LO QUE QUIERO LOGRAR ES DANDO COMO REFERENCIA EL SERVICIO Y SU CONDICION OBTERNER UNA LISTA DE TODAS LAS CASAS QUE CUMPLEN CON ESTAS CONDICIONES. LOS VALORES DENTRO DE TABLA SON REPETIDOS. TE AGREGO UN EJEMPLO RESUMIDO DE LA TABLA . la busqueda la hago en una hoja distinta a la que esta la table con los datos. Por lo que me es importante conseguirlo con formulas...

Se trata entonces, a partir de una tabla cruzada, obtener un listado de elementos que cumplen con dos condiciones. Veamos dicha tabla:

Nuestro objetivo es lograr un listado de los elementos del rango A3:A7, es decir, del campo 'CASAS' de la tabla, que cumplan unas condiciones definidas por nosotros, de acuerdo a las otras dos variables de la tabla; en este caso según el tipo de consumo (LUZ, GAS, AGUA) y si dicho consumo se encuentra CONECTADO/DESCONECTADO para cada CASA..
Para ello asignaremos nombre a los siguientes rangos(ver) dentro de nuestra hoja de trabajo 'datos:
AGUA =datos!$D$3:$D$7
CASAS =datos!$A$3:$A$7
consumos =datos!$B$2:$D$2
GAS =datos!$C$3:$C$7
LUZ =datos!$B$3:$B$7
En la siguiente etapa definiremos con la Validación de datos los criterios para las celdas G7 y G9; donde para la celda G7 se le asignará una validación tipo lista con los valores del rango consumos; y para la celda G9 una validación tipo lista con los valores CONECTADO / DESCONECTADO.

haz click en la imagen

Los valores que despleguemos en estas dos celdas (G7 y G9 servirán para configurar nuestra fórmula, y poder obtener así el listado de CASAS que cumplen ambos criterios.
Nuestra fórmula deberá ser matricial(Ctrl+Mayusc+Enter), y la desarrollaremos para el rango G12:G16
{=SI(INDIRECTO(G7)=$G$9;CASAS;"")}
con la que conseguimos que detecte para el rango definido en la celda G7 los valores iguales al valor determinado en la celda validada G9, devolviendo en caso de coincidencia el correspondiente del rango CASAS, y blanco en caso contrario.
Para que Excel reconozca el valor de la celda G7 como un rango evaluable se ha de emplear la función INDIRECTO.
El resultado será entonces:

haz click en la imagen

Es decir, sólo los DPTO1, DPTO3 y DPTO4 tienen la LUZ en modo CONECTADO.