Búsqueda compleja en una tabla

Una editorial publica cinco revistas de otros tantos deportes: boxeo, ciclismo, fútbol, natación y tenis. Los clientes se suscriben a una o más revistas y los datos se almacenan en una tabla (B2:H10):

El objetivo del ejercicio es elegir un cliente (C12) y obtener su número de registro (G12) y la lista de revistas a las que está suscrito (C14:G14).

Como en otras ocasiones, resolveremos el problema usando celdas auxiliares (J3:N9); luego, agruparemos todas las fórmulas en una fórmula compuesta. Las celdas auxiliares ya no serán necesarias y las podremos eliminar.

Combinamos las celdas C12 y D12 dejando espacio suficiente para los nombres (Inicio + Combinar y centrar). Justificamos el texto a la izquierda.

Hacemos clic en C12, accedemos a Datos + Validación de datos y asociamos la lista de nombres (B3:B10) a la celda combinada:

Elegimos un nombre cualquiera; por ejemplo, Juan Gómez

En G12 ponemos el número de afiliado del suscriptor seleccionado:

=BUSCARV(C12;B3:C10;2;FALSO)     [Resultado: 1389]

En K3 obtendremos el número de la fila en la que está la persona elegida:

=COINCIDIR(C12;B3:B10;0)+2     [Resultado: 6]

Ahora, debemos comprobar qué celdas del rango D6:H6 contienen una "X" (son las suscripciones del afiliado). El rango D6:H6 lo formaremos con DIRECCION; así, DIRECCION(K3;4) devolverá $D$6; DIRECCION(K3;8) devolverá $H$6. Combinando ambas fórmulas de esta manera: DIRECCION(K3;4)&":"&DIRECCION(K3;8), obtendremos $D$6:$H$6. Con INDIRECTO obtenemos los valores de las celdas, y con HALLAR comprobaremos qué celdas tienen "X"; las que tengan "X" devolverán un 1; las restantes, devolverán un error.

Seleccionamos J5:N5 y escribimos:

=HALLAR("X";INDIRECTO(DIRECCION(K3;4)&":"&DIRECCION(K3;8)))     [Terminamos con Ctrl + Mayús + Intro]

En la siguiente fila numeraremos (del 1 al 5) las celdas en las que hemos encontrado una "X".

Seleccionamos J6:N6 y escribimos:

=SI(ESNUMERO(J5:N5);{1\2\3\4\5};"")     [Terminamos con Ctrl + Mayús + Intro]

Con esta fórmula matricial comprobamos si hay un número en cada celda del rango J5:N5 y, en caso de haberlo, le asignamos el valor correspondiente de la matriz {1\2\3\4\5}.

En J7:N7 obtenemos el número menor, el segundo menor, el tercero... de los números de rango J6:N6.

Seleccionamos J7:N7 y escribimos:

=K.ESIMO.MENOR(J6:N6;{1\2\3\4\5})      [Terminamos con Ctrl + Mayús + Intro]

Comparando esta lista de números con los valores del encabezado (D2:H2) obtendremos las revistas a las que se ha suscrito el cliente.

Seleccionamos J8:N8 y escribimos:

=INDICE(D2:H2;1;J7:N7)      [Terminamos con Ctrl + Mayús + Intro]

Seleccionamos J9:N9 y escribimos la primera fórmula compuesta:

=INDICE(D2:H2;1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR("X";INDIRECTO(DIRECCION(COINCIDIR(C12;B3:B10;0)+2;4)&":"&DIRECCION(COINCIDIR(C12;B3:B10;0)+2;8))));{1\2\3\4\5};"");{1\2\3\4\5}))      [Terminamos con Ctrl + Mayús + Intro]

La fórmula definitiva la pondremos en C14:G14. Utilizaremos la función SI.ERROR para eliminar los valores #¡NUM! que hay en J9:N9.

Seleccionamos C14:G14 y escribimos la segunda y definitiva fórmula compuesta:

=SI.ERROR(INDICE(D2:H2;1;K.ESIMO.MENOR(SI(ESNUMERO(HALLAR("X";INDIRECTO(DIRECCION(COINCIDIR(C12;B3:B10;0)+2;4)&":"&DIRECCION(COINCIDIR(C12;B3:B10;0)+2;8))));{1\2\3\4\5};"");{1\2\3\4\5}));"")      [Terminamos con Ctrl + Mayús + Intro]

Esta fórmula es definitiva; no se necesitan las columnas J a N. Podemos eliminarlas tranquilamente.

Nota: Como en Excel 2003 no existe la función SI.ERROR, podemos utilizar el formato condicional para poner a las celdas que contienen #¡NUM! el mismo color que el del fondo; de este modo, no se verá el código de error.

Si no queremos usar fórmulas matriciales y no nos importa conservar las celdas auxiliares, podemos resolver el ejercicio de la manera que se muestra a continuación:

En C12, G12 y K3 mantenemos las mismas fórmulas y elegimos a Martín Redondo.

En J5:

=SI(INDIRECTO(DIRECCION($K$3;COLUMNA(D1)))="X";D2;"")

Extendemos la fórmula hacia la derecha.

Martín Redondo se ha suscrito a las revistas de ciclismo, natación y tenis.

En J6:

=(J5<>"")*COLUMNA(D2)

Extendemos la fórmula hacia la derecha.

Hemos obtenido las columnas en las que están los nombres de las revistas de Martín Redondo.

En J7:

=JERARQUIA(J6;J6:N6;0)

Extendemos la fórmula hacia la derecha.

Hemos ordenado jerárquicamente la fila anterior.

En J8:

=INDICE($J$5:$N$5;0;COINCIDIR(COLUMNA(A1);$J$7:$N$7;0))

Extendemos la fórmula hacia la derecha.

Ya hemos conseguido los nombres de las revistas, aunque en orden invertido con respecto al original. Sólo nos falta eliminar el error #N/A. Lo haremos en C14:G14.

En C14:

=SI.ERROR(J8;"")

Extendemos la fórmula hacia la derecha.

Descargar archivo (S21-Fórmula compleja.xls)

Descargar archivo (S21-Fórmula compleja.xlsx)