Fórmula do Excel para retornar o valor da grade

Question

Eu tenho alguns dados parecidos com isso

A B C D E F G
B C D E F G A
C D E F G A B
D E F G A B C 
E F G A B C D
F G A B C D E 
G A B C D E F 

Além disso, tenho um dado que se parece com isso:

A 
B 

O que preciso pesquisar é o cabeçalho da linha na qual o valor principal reside, quando o cabeçalho da coluna é o valor inferior. Por exemplo, neste exemplo, eu olharia na primeira linha para encontrar B , então eu olharia para baixo a coluna até encontrar A , então eu olharia para o cabeçalho da linha para retornar G

 A  (B) C D E F G
 B   C  D E F G A
 C   D  E F G A B
 D   E  F G A B C 
 E   F  G A B C D
 F   G  A B C D E 
(G) (A) B C D E F 

Eu tentei usar HLOOKUP , INDEX , MATCH , etc., mas continuo correndo em um obstáculo quando preciso fornecer uma matriz para a coluna inteira.

Aqui está um exemplo da sintaxe que está me dando erros:

=CONCAT(address(8,1):address(12,1))

Eu não posso ter o : entre meus dois endereços de célula.

Alguma ideia?

    

Answer 1

Tenho certeza de que há maneiras mais elegantes de fazer isso, mas aqui está uma solução rápida e suja.

Eucoloqueisuagradeemumlocalarbitrário(B2:H8).J2eJ3sãoseusvaloresdepesquisaeoresultadoestáemJ4.OqueestánacolunaLécomponentesdarespostaparafinsdeexplicação,porqueafórmulaélonga.

AprimeiraparteestáemL3,encontrandoametaBnalinhasuperior:

=MATCH(J3,B2:H2,0)

EleencontraoalvoBnacoluna2damatrizeissoéusadonapróximaetapadelocalizarAnessacoluna.L2contém:

=MATCH(J2,OFFSET(B2,0,L3-1,7,1),0)

OFFSETestáancoradonocantosuperioresquerdodamatriz(B2).AcolunaemqueBfoiencontradaeraasegundacolunadamatriz,masissoéumdeslocamentode1daprimeiracoluna,daío-1(adoroquandoconsigousar"consequentemente" em uma sentença) . OFFSET permite especificar um intervalo usando parâmetros de altura e largura. O 7 é a altura da matriz neste exemplo.

A MATCH encontra o alvo A na linha 7 dessa coluna da matriz.

Agora, basta ir para a linha da primeira coluna da matriz:

=INDEX(B2:B8,L2,0)

Lá, você encontra sua resposta: G

Você pode substituir as fórmulas dos componentes pelos componentes para obter uma fórmula autônoma e autônoma:

=INDEX(B2:B8,MATCH(J2,OFFSET(B2,0,MATCH(J3,B2:H2,0)-1,7,1),0),0)