Excel INDEX MATCH Verificando várias colunas

Question

O problema que estou essencialmente tentando resolver é um VLOOKUP que está verificando as Colunas A: E para um valor e retornando o valor mantido na Coluna F, caso seja encontrado em qualquer um destes.

Como o VLOOKUP não está à altura da tarefa, examinei a sintaxe INDEX-MATCH, mas estou com dificuldades para entender como concluir isso para uma matriz de valores, em oposição a uma única coluna. Eu criei um conjunto de dados de exemplo abaixo para tentar explicar isso:

A------B------C------D------E------F

1------2------3------4------5------Apple

12-----13--------------------------Banana

14---------------------------------Carrot

Se a célula que está sendo checada contiver 1,2,3,4 ou 5, o resultado da fórmula deve ser Apple. Se for 12 ou 13, deve retornar Banana e, finalmente, se contiver 14, deverá retornar Carrot.

A segunda metade disso vem do fato de que a célula que está sendo referenciada não é um valor único, mas uma tabela completa em si. Como tal, esta pesquisa será completada um grande número de vezes de acordo com valores diferentes.

Então, para demonstrar, há outra tabela em outro lugar (como abaixo) que possui esses valores. Eu estou tentando fazer com que o sistema identifique qual linha e, portanto, quais dos valores "Apple, Banana, Carrot" para associar coluna. A tabela ficaria como abaixo

H ------ I ------------

1 ------ (Apple) ----

2 ------ (Apple) ----

12 ----- (Banana) -

etc .-----------------

Os valores entre colchetes são onde a fórmula calcula esses valores.

    

Answer 1

Com base na minha própria pesquisa & discussões com @ Gary'sStudent, a solução que usei foi criar uma fórmula MATCH para cada uma das possíveis colunas em que o valor poderia estar contido, junto com uma instrução "IFERROR" de captura em branco.

I1 =IFERROR(MATCH($H1,A$1:A$3,0),"")     
J1 =IFERROR(MATCH($H1,B$1:B$3,0),"")     
K1 =IFERROR(MATCH($H1,C$1:C$3,0),"")    
L1 =IFERROR(MATCH($H1,D$1:D$3,0),"")    
M1 =IFERROR(MATCH($H1,E$1:E$3,0),"")
etc.

Essas colunas agora podem ser ocultadas para evitar confusão / interação do usuário.

Eu então criei um índice que os acumula em um único valor, que deve corresponder ao ROW em questão. Novamente, há uma verificação (primeiro SUM) para inserir isso como um valor em branco se o valor não for encontrado na tabela.

N1 =IF(SUM(I1:M1)=0,"",INDEX($A$1:$F$3,SUM(I1:M1),6))

Porfim,inseriumafórmuladeformataçãocondicionalparagarantirqueousuárioidentifiqueesubstitua/removaquaisquerdadosduplicados.

A1:E3Cellcontainsablankvalue[FormattingNoneSet,StopifTrue]A1:E3=COUNTIF($A$1:$E$3,A1)>1[FormattingText:White,Background:Red]H1:N1=COUNTIF($A$1:$E$3,H1)>1[FormattingText:Red,Background:Red]

Issoéapenasumasugestãoparaousuárioremoveressesdadosduplicados.

    

Answer 2

Você tem vários casos diferentes. Vamos considerar um caso:

Em algum lugar nas colunas A através de E existe uma e somente célula contendo 13, retorna o conteúdo da célula na coluna F no mesma linha.

Nós usaremos uma coluna "auxiliar". Em G1 digite:

=COUNTIF(A1:E1,13)

e copie para baixo. Isso nos permite identificar a linha:

AgorapodemosusarMATCH()/INDEX():

Escolhaumacélulaedigite:

=INDEX(F:F,MATCH(1,G:G,0))

Seas"regras" forem alteradas e houver mais de 13 em uma linha ou várias linhas contendo 13, modificaremos a coluna auxiliar.

EDIT # 1:

Com base em sua atualização, o primeiro passo seria retirar as codificadas 13 das fórmulas na coluna "auxiliar" e colocá-las em sua própria célula (por exemplo H1 ) . Em seguida, você pode executar diferentes casos simplesmente alterando uma única célula.

Se você tiver um grande número de casos em uma tabela, poderá criar uma macro para configurar cada caso (atualizar H1 ) e registre os resultados.

    

Answer 3

Para uma única fórmula em H1:

=INDEX($F$1:INDEX(F:F,MATCH("ZZZ",F:F)),AGGREGATE(15,6,ROW($A$1:INDEX(E:E,MATCH("ZZZ",F:F)))/($A$1:INDEX(E:E,MATCH("ZZZ",F:F))=H1),1))

Essa é uma fórmula de matriz, portanto, precisamos restringir as referências ao tamanho do conjunto de dados. Todos os INDEX(E:E,MATCH("ZZZ",F:F)) fazem isso. Isso retorna a última linha na coluna F com texto. Em seguida, define essa como a última linha para iterar.

@ O método Gary'sStudent evita fórmulas de matriz e pode ser o método necessário. À medida que o Dataset e o número de fórmulas aumentam, também aumenta o tempo para cálculos. Até mesmo, em algum momento, o crash do Excel. Geralmente isso leva alguns milhares, mas eu quero fazer o aviso.

EDITAR

ParaevitarousodefórmulasdoArrayeaindaserumafórmula:

=IFERROR(INDEX(F:F,MIN(IFERROR(MATCH($H1,A:A,0),1050000),IFERROR(MATCH($H1,B:B,‌​0),1050000),IFERROR(MATCH($H1,C:C,0),1050000),IFERROR(MATCH($H1,D:D,0),1050000),I‌​FERROR(MATCH($H1,E:E,0),1050000))),"")

Isso é baseado na resposta do OP, apenas combinou esse método em uma fórmula.

Esta fórmula irá ignorar entradas duplicadas e retornar a primeira linha na qual o número é encontrado.

E como é uma matriz, as referências de coluna completa não prejudicam os tempos de cálculo.

    

Answer 4

Um método diferente seria baseado em uma tabela auxiliar, que representa como isso "deveria" ter sido estruturado em primeiro lugar. Isso evitaria as equações de monstros que são irritantes para depurar e alterar depois, e é capaz de resolver de maneira limpa um número variável de colunas, ao contrário da ideia de ter 5 colunas de pesquisa.

Se o acima estiver na Planilha1, adicione uma Planilha2. Nesse lugar quatro colunas; Linha, coluna, ID, nome

A fórmula em Row deve ser (no código psuedo, "Last" significa "para a linha acima em sheet2")

=IF(Column = 1, Last row + 1 , Last row)

Fórmula em Column :

=IF(OR(Last Column = 5; INDEX(StartTable, last row, last column + 1) = ""), 1, Last column+1)

Fórmula em ID e Name :

=INDEX(StartTable, Row, Column)    
=INDEX(NameColumn, Row, 1)

Em seguida, você preenche esse valor (basicamente até row > número de linhas na tabela original).

Finalmente, você usa a nova tabela com um vlookup ou índice / correspondência comum.

PRO: Fórmulas muito mais simples, fáceis de usar e entender.

CONS: Precisa de uma mesa extra, deve manter o comprimento da mesa. Em termos de desempenho, existe um risco, pois isso praticamente requer um único thread para toda a "cadeia" de valores.

Além disso, se algumas linhas de erro forem aceitáveis, o código pode ser um pouco mais simples e possivelmente mais performático, então podemos supor que o número de colunas sempre é 5, dando tanto a linha quanto a coluna.