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

Question

Isso é em parte por diversão, e espero que não se feche por motivos de subjetividade porque, embora "qual a solução mais elegante" seja subjetiva, "a solução atual é complicada e feia" é clara para todos.

De qualquer forma, eu 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 devolver o col B na Folha 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. Isso funciona, eu vou dar isso ...

Como você abordaria esse problema?

    

Answer 1

Aqui está a minha solução. Index + match pode ser usado para procurar valores em uma tabela nos dois sentidos. Isso ajusta a solução normal para que encontre o x e retorne 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))

Eucolocotudoemumafolhaparafacilitaravisualizaçãoetambémtornaafunçãoumpoucomaiscurta.

DeacordocomocomentáriodeBarry,umaopçãomelhorseriaevitarodeslocamentocomoutrafunçãodeíndice,paraquenãosejavolá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, tenho

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

na coluna B, retornando a linha contendo a coisa na folha2. Por enquanto, tudo bem. Mas para então retornar a posição do x dentro daquela linha na folha 2, e dar a palavra correta, eu tenho essa monstruosidade, usando uma string de referência R1C1 concatenada dentro de uma função indireta para converter o número da linha em uma referência que eu posso 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 [coluna]", que alimenta o indireto (converte para uma referência normal) que então vai para a instrução if para converter a presença do x na palavra relevante

    

Answer 3

Aqui está minha solução preferida, conceito similar ao do gtwebb (e supondo que seus dados estejam alinhados como na captura de tela dele), mas não usa OFFSET (que eu acredito que só deve ser usado quando não há outras opções porque é < um href="https://msdn.microsoft.com/en-us/library/office/bb687891.aspx"> volátil ).

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