☰

Búsqueda en varias tablas de Excel, ¿puede encontrar una solución más elegante?

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function vlookup lookup
Búsqueda en varias tablas de Excel, ¿puede encontrar una solución más elegante?

Esto es en parte por diversión, y espero que no se cierre por motivos de subjetividad porque, aunque "qué solución es la más elegante" es subjetivo, "la solución actual es complicada y fea" es evidente para todos.

De todos modos, tengo una lista en la hoja 1 así:

*Thing*            *type of thing*
Apple
Orange
Larceny
Cake
Banana
Murder

y en la hoja 2 así:

              fruit    confectionary      crime
Apple           x                                
Orange          x                                
Larceny                                     x    
Cake                         x                   
Banana          x                                
Murder                                      x

y quiero devolver la columna B en la Hoja 1 así

*Thing*               *type of thing*                                   
Apple                 fruit                        
Orange                fruit                        
Larceny               crime                        
Cake                  confectinary               
Banana                fruit                        
Murder                crime

Publicaré mi solución actual como respuesta. Funciona, lo daré...

¿Cómo abordarías este problema?

Respuesta1

Aquí está mi solución. Index+match se puede utilizar para buscar valores en una tabla en ambos sentidos. Esto modifica la solución normal para que encuentre la x y devuelva el encabezado de la columna.

=INDEX($E$1:$G$1,MATCH("x",OFFSET($E$1:$G$1,MATCH(A2,$D$2:$D$7,0),),0))

ingrese la descripción de la imagen aquí

Pongo todo en una hoja para que sea más fácil de ver y también acorta un poco la función.

Según el comentario de Barry a continuación, una mejor opción sería evitar el desplazamiento con otra función de índice para que no sea volátil.

=INDEX($E$1:$G$1,MATCH("x",INDEX($E$2:$G$7,MATCH(A2,$D$2:$D$7,0),0),0))

Respuesta2

Para mi solución actual tengo

=MATCH(A1,Sheet1!A1:A10,0)

en la columna B, devolviendo la fila que contiene el objeto en la hoja2. Hasta ahora, todo bien. Pero para luego devolver la posición de la x dentro de esa fila en la hoja 2, y dar la palabra correcta, tengo esta monstruosidad, usando una cadena concatenada de referencia R1C1 dentro de una función indirecta para convertir el número de fila en una referencia que realmente puedo usar. :

=IF(INDIRECT("sheet2!R"&B1&"C2",0)="X","fruit",IF(INDIRECT("sheet2!R"&B1&"C3",0)="X","confectionary",IF(INDIRECT("sheet2!R"&B1&"C4",0)="X","crime","ERROR")))

Luego, la cadena se evalúa como "hoja2R[número de fila]C[columna]", que pasa a la indirecta (se convierte en una referencia normal) que luego pasa a la declaración if para convertir la presencia de la x en la palabra relevante.

Respuesta3

Aquí está mi solución preferida, un concepto similar al de gtwebb (y suponiendo que sus datos estén alineados como en su captura de pantalla) pero no usa OFFSET(lo cual creo que solo debe usarse cuando no hay otras opciones porque esvolátil).

=INDEX($E$1:$G$1,MATCH("x",$E2:$G2,0))

información relacionada