Pesquisa de várias tabelas do Excel, você consegue encontrar uma solução mais elegante?

Question

Isto é em parte por diversão, e espero que não seja encerrado por motivos de subjetividade, porque embora "qual solução é a mais elegante" seja subjetivo, "a solução atual é complicada e feia" é evidente para todos verem.

De qualquer forma, tenho uma lista na planilha1 assim:

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

e na folha 2 assim:

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

e eu quero retornar a coluna B na planilha 1 assim

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

Vou postar minha solução atual como resposta. Funciona, vou admitir...

Como você abordaria esse problema?

Answer 1

Aqui está minha solução. Índice+correspondência pode ser usado para procurar valores em uma tabela nos dois sentidos. Isso ajusta a solução normal para encontrar x e retornar o cabeçalho da coluna.

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

Coloquei tudo em uma folha para ficar mais fácil de ver e também deixar a função um pouco mais curta.

De acordo com o comentário de Barry abaixo, uma opção melhor seria evitar o deslocamento com outra função de índice para que não seja volátil.

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

Answer 2

Para minha solução atual eu tenho

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

na coluna B, retornando a linha que contém o item na planilha2. Até agora tudo bem. Mas para retornar a posição do x dentro daquela linha na planilha 2 e fornecer a palavra correta, eu tenho essa monstruosidade, usando um R1C1 referenciando uma string concatenada dentro de uma função indireta para converter o número da linha em uma referência que eu possa realmente 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")))

A string então é avaliada como "sheet2R[rownumber]C[column]", que alimenta o indireto (converte em uma referência normal) que então vai para a instrução if para ocultar a presença do x na palavra relevante

Answer 3

Aqui está minha solução preferida, conceito semelhante ao do gtwebb (e assumindo que seus dados estão alinhados como na captura de tela dele), mas não usa OFFSET(o que acredito que só deve ser usado quando não houver outras opções, porque évolátil).

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