Excel INDEX MATCH Verificando múltiplas colunas

Question

O problema que estou essencialmente tentando resolver é um VLOOKUP que verifica as Colunas A:E em busca de um valor e retorna o valor mantido na Coluna F, caso seja encontrado em alguma delas.

Como VLOOKUP não está à altura da tarefa, examinei a sintaxe INDEX-MATCH, mas estou lutando para entender como concluir isso para uma matriz de valores, em vez de uma única coluna. 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

Caso a célula verificada contenha 1,2,3,4 ou 5, o resultado da fórmula deverá ser Apple. Se for 12 ou 13 deverá retornar Banana e por fim se contiver 14 deverá retornar Cenoura.

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

Então, para demonstrar, há outra tabela em outro lugar (como abaixo) que contém esses valores. Estou tentando fazer com que o sistema identifique qual linha e, portanto, qual dos valores "Maçã, Banana, Cenoura" associar a cada coluna. A tabela ficaria como abaixo

OI------------

1------(maçã)----

2------(maçã)----

12-----(Banana)-

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

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

Answer 1

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

Em algum lugar nas colunasAatravésEexiste uma e única célula contendo 13, retorne o conteúdo da célula na colunaFna mesma linha.

Usaremos uma coluna "auxiliar". EmG1digitar:

=COUNTIF(A1:E1,13)

e copie. Isso nos permite identificar a linha:

Agora podemos usarCORRESP()/ÍNDICE():

Escolha uma célula e digite:

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

Se as "regras" mudarem e puder haver mais de um 13 em uma linha ou várias linhas contendo 13, modificaríamos a coluna auxiliar.

EDITAR#1:

Com base na sua atualização, o primeiro passo seria extrair o código fixo13das fórmulas da coluna "auxiliar" e coloque-a em sua própria célula,(dizerH1). Então você pode executar casos diferentes 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(atualizarH1)e registre os resultados.

Answer 2

Com base em minhas próprias pesquisas e discussões com @Gary'sStudent, a solução que usei foi criar uma fórmula MATCH para cada uma das colunas possíveis nas quais 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.

Criei então um índice que os acumula em um único valor, que deve corresponder ao ROW em questão. Novamente, há uma verificação (primeira 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))

Por fim, inseri algumas fórmulas de formatação condicional para garantir que o usuário identifique e substitua/remova quaisquer dados duplicados.

A1:E3 Cell contains a blank value                [Formatting None Set, Stop if True]
A1:E3 =COUNTIF($A$1:$E$3,A1)>1                   [Formatting Text:White, Background:Red]

H1:N1 =COUNTIF($A$1:$E$3,H1)>1       [Formatting Text:Red, Background:Red]

Esta é apenas uma dica para o usuário remover esses dados duplicados.

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))

Esta é uma fórmula de matriz, portanto precisamos limitar as referências ao tamanho do conjunto de dados. Todos INDEX(E:E,MATCH("ZZZ",F:F))fazem isso. Isso retorna a última linha da coluna F que contém texto. Em seguida, ele define isso como a última linha a ser iterada.

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

---

EDITAR

Para evitar o uso de fórmulas de matriz e ainda ser uma fó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 se baseia 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 em que o número for encontrado.

E porque não é uma matriz, as referências de coluna completa não são prejudiciais aos tempos de cálculo.

Answer 4

Um método diferente seria baseado em uma tabela auxiliar, que representa como esse “deveria” ter sido estruturado em primeiro lugar. Isso evitaria as equações monstruosas que são irritantes para depurar e alterar posteriormente, e é capaz de resolver de forma 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. Naquele lugar quatro colunas; Linha, Coluna, ID, Nome

A fórmula Rowdeve ser (no código psuedo, "Último" significa "para a linha acima na planilha2")

=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 IDe Name:

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

Em seguida, você preenche isso (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, mais fáceis de usar e entender.

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

Além disso, se algumas linhas de erro estiverem corretas, o código pode ser um pouco mais simples e possivelmente com melhor desempenho, podemos então assumir que o número de colunas sempre é 5, fornecendo row e column .