Excel: Por que o VLOOKUP está retornando resultados inconsistentes e inesperados?

Question

Estou automatizando uma coluna do Excel para atribuir valores de conta com base no fornecedor pago. Por exemplo, se a AT & T é paga, o valor da conta é Utilities:Telephone e, quando a agência postal é paga, o valor da conta é codificado com Postage and Shipping . Eu estou usando a seguinte fórmula

=VLOOKUP(B2,$E$2:$F$8,2,1)

para calcular o valor na coluna Notes . o ExpectedValue é codificado e Valid baseia-se ou não Notes é igual ao meu ExpectedValue

Se eu fizesse o que estava ESPERANDO para fazer, o Description será correspondido com meu LookupValue e, se for uma correspondência aproximada, o campo será preenchido com o valor correspondente de DecodedValue .

Com base nisso, a linha 1 é OK . No entanto, as linhas 2-8 são BAD , de alguma forma as linhas são correspondentes e decodificadas como o primeiro valor de índice na minha tabela de pesquisa. Como as linhas que começam com PAYPAL são todas OK , pensei que podem ser os espaços ou a pontuação na tabela de pesquisa. Mas, se for esse o caso, as linhas 5, 8 e amp; 15 deve passar. Então eu pensei que era apenas a correspondência da pesquisa, se o valor de pesquisa estava no início da célula. Mas a linha 1 não passaria.

15 linhas de dados CSV são coladas abaixo e mencionadas acima.

Line,Description,Notes,ExpectedValue,Valid,LookupValue,DecodeValue
1,DDA PUR ATT*PAYMEN 800-331-0500 TX 300100860296,Utilities:Telephone,Utilities:Telephone,OK,ATT*PAY,Utilities:Telephone
2,DDA PUR THE HOME D MILWAUKEE    WI,Utilities:Telephone,Repairs,BAD,NETFLIX,Supplies:Research
3,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,PAYPAL,Supplies
4,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,STONE CREE,Craft Service
5,DDA PUR WALGREENS SHOREWOOD    WI,Utilities:Telephone,Medical,BAD,WALGREENS,Medical
6,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,THE HOME D,Repairs
7,DDA PUR STONE CREE SHOREWOOD    WI,Utilities:Telephone,Craft Service,BAD,USPS,Postage and Shipping
8,DDA PUR NETFLIX.CO NETFLIX.COM  CA,Utilities:Telephone,Supplies:Research,BAD,,
9,PAYPAL           INST XFER,Supplies,Supplies,OK,,
10,PAYPAL           INST XFER,Supplies,Supplies,OK,,
11,PAYPAL           INST XFER,Supplies,Supplies,OK,,
12,PAYPAL           INST XFER,Supplies,Supplies,OK,,
13,PAYPAL           INST XFER,Supplies,Supplies,OK,,
14,DDA PUR THE HOME D MILWAUKEE    WI,Utilities:Telephone,Repairs,BAD,,
15,DDA PUR USPS 56064 MILWAUKEE    WI,Utilities:Telephone,Postage and Shipping,BAD,,

    

Answer 1

Como já explicado nos comentários, o range_lookup paramater de VLOOKUP não é uma pesquisa difusa. Em vez disso, ele pressupõe que a lista está em ordem crescente e encontra a correspondência mais próxima.

Aqui está a fórmula de matriz que você deveria ter usado (entrou com CTRL + ALT + ENTER ) usado

{=IF(MIN(LEN(SUBSTITUTE($B2,$F$2:$F$8,"")))=MAX(LEN(SUBSTITUTE($B2,$F$2:$F$8,""))),"",INDEX($G$2:$G$8,MATCH(MIN(LEN(SUBSTITUTE($B2,$F$2:$F$8,""))),LEN(SUBSTITUTE($B2,$F$2:$F$8,"")),0)))}

E aqui está como funciona:

Uma parte da fórmula se repete bastante: LEN(SUBSTITUTE($B2,$F$2:$F$8,""))
(Eu só vou usar LEN(...) no futuro para economizar espaço)
Ele nos fornece uma matriz de números inteiros com base na substituição de cada LookupValue por espaços em branco no Description e na medição do tamanho do resultado. Quanto menor o comprimento, mais o texto é correspondido. Tomando o MIN desse array nos informa o maior LookupValue que existe no Description . Obviamente, isso também significa que THE HOME D será sempre vencido por THE HOME DESIGNERS ou algo assim, tenha cuidado / exija seu campo LookupValue .

Tirando essa parte do caminho, vamos mostrar a fórmula assim para simplificar:

{=IF(MIN(LEN(...)))=MAX(LEN(...))),"",INDEX($G$2:$G$8,MATCH(MIN(LEN(...))),LEN(...)),0)))}

A primeira parte IF(MIN=MAX) verifica se pelo menos um dos registros LookupValue existe na descrição. Se nada acontecer, o SUBSTITUTE não fará nada e todo LEN será o mesmo.

INDEX($G$2:$G$8,MATCH(MIN(LEN(...))),LEN(...)),0))

Assim que passarmos nessa verificação, a carne real estará na combinação INDEX + MATCH . Combinamos o menor comprimento (ou seja, o maior LookupValue que existe no Description ) no array de comprimentos para usar o primeiro LookupValue que foi encontrado. Conecte isso em uma matriz do DecodeValue e saia do valor Notes que você deseja.

O ponto chave é tornar seu campo LookupValue o mais longo e exato possível para evitar correspondências duplicadas e, então, tudo deve funcionar bem.

Esta é a versão do CSV dos meus resultados:

Line,Description,Notes,ExpectedValue,Valid,LookupValue,DecodeValue
1,DDA PUR ATTPAYMEN 800-331-0500 TX 300100860296,Utilities:Telephone,Utilities:Telephone,OK,ATTPAY,Utilities:Telephone
2,DDA PUR THE HOME D MILWAUKEE WI,Repairs,Repairs,OK,NETFLIX,Supplies:Research
3,DDA PUR STONE CREE SHOREWOOD WI,Craft Service,Craft Service,OK,PAYPAL,Supplies
4,DDA PUR STONE CREE SHOREWOOD WI,Craft Service,Craft Service,OK,STONE CREE,Craft Service
5,DDA PUR WALGREENS SHOREWOOD WI,Medical,Medical,OK,WALGREENS,Medical
6,DDA PUR STONE CREE SHOREWOOD WI,Craft Service,Craft Service,OK,THE HOME D,Repairs
7,DDA PUR STONE CREE SHOREWOOD WI,Craft Service,Craft Service,OK,USPS,Postage and Shipping
8,DDA PUR NETFLIX.CO NETFLIX.COM CA,Supplies:Research,Supplies:Research,OK,,
9,PAYPAL INST XFER,Supplies,Supplies,OK,,
10,PAYPAL INST XFER,Supplies,Supplies,OK,,
11,PAYPAL INST XFER,Supplies,Supplies,OK,,
12,PAYPAL INST XFER,Supplies,Supplies,OK,,
13,PAYPAL INST XFER,Supplies,Supplies,OK,,
14,DDA PUR THE HOME D MILWAUKEE WI,Repairs,Repairs,OK,,
15,DDA PUR USPS 56064 MILWAUKEE WI,Postage and Shipping,Postage and Shipping,OK,,