Como você obtém uma matriz de valores de vlookup sem uma coluna extra no Microsoft Excel?

Question

Eu tenho uma lista e uma tabela de pesquisa. A lista estará na coluna A e a tabela de consulta estará nas colunas C e D. Cada célula na coluna C contém texto e é exclusiva. A coluna D tem um valor correspondente para cada elemento da coluna C. A coluna A contém uma lista não classificada com duplicatas do texto da coluna C, mas nada fora da coluna C. Um exemplo fácil é mostrado abaixo

O que eu quero, em última instância, é uma matriz que contenha os valores correspondentes pesquisados da tabela para toda a coluna A. Portanto, neste exemplo, desejo

{1,2,1,2,2,2,1,1,2}

Eu sei que isso pode ser feito usando

=vlookup(B9,$C$1:$D$2,2,FALSE)

em cada célula da coluna B e pegue a coluna como uma matriz {$ B $ 1: $ B $ 9}. No entanto, eu preciso pular o intermediário e omitir ter qualquer coisa em outra coluna, pois isso precisa ser executado em várias tabelas de consulta simultâneas, mas não conectadas, ou seja, não uma pesquisa múltipla, eu só preciso olhar para a mesma lista A sob um diferente conjunto de valores correspondentes D.

Eu estou em uma perda completa, parece tão simples, mas eu tenho tentado por horas e indo em círculos. Lembro-me de ler que você não pode enviar vlookup para um array e também não tive muita sorte em tentar usar o match. Então realmente qualquer ajuda seria apreciada! Obrigada!

    

Answer 1

Não há como fazer isso sem macros.

Você não pode evitar usar VLOOKUP e não pode gerenciar o array como o parâmetro "lookup value".

Acho que a melhor opção é usar uma coluna de ajuda como você já descreveu.

    

Answer 2

Isso é possível com uma única fórmula em uma única célula usando uma função do Excel relativamente nova TEXTJOIN . Isso funciona com o Excel 2016, mas não é reconhecido no Excel 2010, resultando em #NAME? Não sei sobre o Excel 2013 e adoraria que alguém o testasse.

Existem maneiras melhores de alcançar o resultado, mas elas não se encaixam em suas restrições, então essa não é a melhor solução para a tarefa, mas é a solução que você queria.

A fórmula é:

="{"&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",FALSE,A1:A9),"a",INDEX(D1:D2,MATCH("a",C1:C2,0))),"b",INDEX(D1:D2,MATCH("b",C1:C2,0)))&"}"

A fórmula acima tem valores de pesquisa codificados. Isso facilita a previsão do resultado, mas também torna a fórmula mais difícil de escrever e manter, além de torná-la mais fraca do que uma fórmula referenciada.

A fórmula abaixo faz referência à célula, por isso não é tão fácil prever a saída, mas é muito mais flexível.

="{"&SUBSTITUTE(SUBSTITUTE(TEXTJOIN(",",FALSE,A1:A9),C1,INDEX(D1:D2,MATCH(C1,C1:C2,0))),C2,INDEX(D1:D2,MATCH(C2,C1:C2,0)))&"}"

A fórmula foi testada usando uma lista com mais de 1.000 linhas sem nenhum problema e imagino que funcionará até o limite de linhas do Excel. O comprimento da fórmula é dimensionado linearmente com a tabela de consulta e pode acomodar pouco mais de 1.000 linhas. E a complexidade permanece constante. Cada nova linha na tabela de pesquisa requer um SUBSTITUTE ( adicional na frente da fórmula e um CELL, INDEX( RANGE (MATCH (CELL, RANGE, 0))) no final. Nada no meio precisa ser mudado.