VLOOKUP é uma função poderosa. Mas eu geralmente recebo uma pergunta em um de meus seminários sobre Power Excel de alguém que quer saber se PROCV pode retornar todos os valores correspondentes. Como você sabe, VLOOKUP com False como quarto argumento sempre retornará a primeira correspondência que encontrar. Na captura de tela a seguir, a célula F2 retorna 3623 porque é a primeira correspondência encontrada para o trabalho J1199.
A questão, então, pode VLOOKUP retornar todas as correspondências?
VLOOKUP não. Mas outras funções podem.
Se você deseja totalizar todos os custos do trabalho J1199, você usaria =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Se você tiver valores de texto e quiser juntar todos os resultados em um único valor, você pode usar =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Esta fórmula só funciona no Office 365 e no Excel 2019.
Ou você pode precisar retornar todos os resultados de um único trabalho em um novo intervalo da planilha. Uma nova =FILTER(B2:C53,A2:A53=K1,"None Found")função que chegará ao Office 365 em 2019 resolverá o problema:
Às vezes, as pessoas desejam realizar todos os VLOOKUPs e soma-los. Se sua tabela de pesquisa estiver classificada, você pode usar =SUM(LOOKUP(B2:B53,M3:N5)).
Se você precisar somar todos os VLOOKUPs com a versão Exact Match do VLOOKUP, você precisará ter acesso a Dynamic Arrays para usar =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Para aprender mais sobre Dynamic Arrays, verifique Excel Dynamic Arrays Straight To The Point.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com, Podcast Episódio 2247: Você pode retornar todos os valores do VLookUp?
Ei. Bem-vindo de volta ao netcast. Sou Bill Jelen. Duas perguntas surgiram em meu seminário em Appleton, Wisconsin, na semana passada - ambas relacionadas. Eles disseram, ei, como retornamos todos os VLOOKUPs, certo? Nesse caso, como J1199 tem um monte de correspondências e eles, você sabe, querem devolvê-las todas, e minha primeira pergunta sempre que alguém me pergunta é, bem, o que você quer fazer com as correspondências? Eles são números que você deseja somar ou é um texto que você deseja concatenar? E é engraçado. As duas perguntas no mesmo seminário, uma pessoa queria somar e a outra queria concatenar os resultados.
Então, vamos dar uma olhada em ambos. Verifique na descrição do YouTube um índice onde você pode pular para o outro se quiser ver o resultado do texto.
Ok, então, em primeiro lugar, se quisermos adicionar todos eles, não vamos usar um PROCV. Vamos usar uma função chamada SUMIF ou SUMIFS que vai somar tudo o que corresponde a este item. Então, SUMIFS. Aqui estão os valores numéricos que queremos somar e pressionarei F4 para bloquear isso. Dessa forma, conforme eu copio isso, ele continuará apontando para o mesmo intervalo, e então queremos ir verificar e ver se o número do JOB na coluna A, novamente F4 ali, é = ao valor à nossa esquerda - neste caso E2 - e à medida que copiamos, veremos o TOTAL de cada item. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Vamos apenas fazer uma pequena verificação aqui. J1199. O total é 25365. Tudo bem. Então, está funcionando. Se forem números e você quiser obter todos os números e somá-los, mude para SUMIF ou SUMIFS, mas se for texto, tudo bem, agora, esta função é nova no Office 365 em fevereiro de 2017. Então, se você tiver o Excel 2016 ou Excel 2013 ou Excel 2010 ou qualquer um dos mais antigos, você não terá esta função. É uma função chamada TEXTJOIN. TEXTJOIN. Esta é outra função de (Joe McDade - 01:50) que acabou de nos trazer todas aquelas ótimas fórmulas de array dinâmico no Ignite em 2018, e Joe garantiu que TEXTJOIN funcionaria com arrays, o que é realmente ótimo.
Então, o delimitador aqui vai ser, ESPAÇO, ignorar VAZIO definitivamente. Queremos ignorar EMPTY aqui porque vamos gerar muitos vazios na próxima parte, a instrução IF. SE aquele item acima de A2, F4, for = para este número de TRABALHO aqui, então eu quero o item correspondente da coluna C, F4, caso contrário, eu quero “” assim. Feche essa instrução IF. Feche o TEXTJOIN. Tenho que pressionar CONTROL + SHIFT + ENTER? Não, eu não. Ele me traz todos os produtos que combinam assim, certo? Então, retornando todos os VLOOKUPs, se quisermos somar, sim, se quisermos concatená-los, sim. (= TEXTJOIN (“,”, Verdadeiro, SE ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, “”)))
Tudo bem, agora, há uma outra possibilidade aqui quando as pessoas me perguntam se podem retornar todos os VLOOKUPs. Pode ser um problema em que queremos procurar cada um desses custos aqui e descobrir o CUSTO DE MANUSEIO e, em seguida, somá-los todos. Tipo, eu não quero colocar uma VLOOKUP aqui e uma VLOOKUP aqui e uma VLOOKUP aqui e uma VLOOKUP aqui. Eu só quero fazer tudo junto e, nesse caso, vamos usar a função SUM e a velha função LOOKUP. LOOKUP diz que vamos procurar todos esses valores na coluna B. Não preciso de F4 aqui porque não estou copiando em lugar nenhum. ,. Aqui está nossa tabela de pesquisa. ), fecha o SUM, e ele sai e faz cada VLOOKUP individual e então soma todos assim. (= SOMA (PROCURAR (B2: B53, K3: L5)))
Bem, ei. Todos esses tópicos são meu livro LIV: The 54 Greatest Tips Of All Time. Clique no i no canto superior direito para saber mais.
Portanto, a questão é: você pode retornar todos os VLOOKUPs? Bem, mais ou menos, mas não usando VLOOKUP. Vamos usar SUMIF, TEXTJOIN ou SUM ou LOOKUP para resolver isso.
Bem, ei. Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
Você sabe, certo, estive falando sobre esses arrays dinâmicos por uma semana. Eu queria fazer um vídeo em que não tocasse em matrizes dinâmicas porque sei que muitas pessoas ainda não as têm, mas aqui estamos. É a saída. Você sabe, não estão em ordem alfabética. Isso seria muito melhor se pudéssemos classificá-los, e se acontecer de você ter os novos arrays dinâmicos, você poderia enviar isso para a função SORT, SORT assim e pressionar ENTER, e agora os resultados serão classificados dessa forma.
Você sabe, até mesmo essa fórmula poderia ficar melhor com os arrays dinâmicos. A pesquisa requer que você use TRUE. E se você quisesse usar um FALSO? Poderíamos mudar isso para PROCV, procurar todo esse texto naquela tabela, 2,. Nesse caso, vou usar TRUE, mas, em outro caso, você pode usar FALSE. CONTROL + SHIFT + ENTER. Não. Vai funcionar, certo? (= SOMA (VLOOKUP (B2: B53, K3: L5,2, Verdadeiro)))
Os Dynamic Arrays lançados no início de 2019 resolverão muitos problemas.
Obrigado por participar do outtake aqui. Nos vemos na próxima vez para outro netcast de.
Baixar arquivo Excel
Para baixar o arquivo excel: can-you-return-all-vlookup-values.xlsx
Quando alguém pergunta "A VLOOKUP pode retornar todas as correspondências, a resposta é Não. Mas há muitas outras funções que podem fazer essencialmente a mesma coisa.
Excel Pensamento do Dia
Pedi conselhos aos meus amigos do Excel Master sobre o Excel. O pensamento de hoje para ponderar:
"Normalize seus dados como você faria com que outros normalizassem seus dados para você"
Kevin Lehrbass
