Você já precisou extrair um valor de um intervalo de acordo com um valor correspondente em uma lista? Por exemplo, talvez você queira extrair dinamicamente o preço de um produto com base no ID dele.
Se o conjunto de dados contiver o ID do produto, você poderá usar uma fórmula para extrair o preço.
Com essa fórmula definida como uma fórmula de coluna, cada linha (e as linhas recém-adicionadas) mostrará o preço correspondente do conjunto de dados do produto.
Diferentes abordagens para a fórmula
É possível extrair dados de um intervalo com base em um valor de pesquisa correspondente de três formas:
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
Veremos como usar essas fórmulas, além dos prós e contras de cada uma.
VLOOKUP
Uma fórmula VLOOKUP procura um valor e retorna um valor na mesma linha, mas a partir de outra coluna (especificada). Este é o formato de uma fórmula VLOOKUP:
=VLOOKUP([Valor de pesquisa], [Conjunto de dados sendo pesquisado], [Número da coluna no conjunto de dados],[Falso ou verdadeiro com base nas necessidades exatas de correspondência])
Para extrair o preço no exemplo acima usando um VLOOKUP, a fórmula ficaria assim:
=VLOOKUP([ID do produto associado]@row, {Product Data | Product}, 4, false)
O intervalo de referência entre planilhas de {Product Data | Product} é assim:
A fórmula retorna os valores na coluna Preço desta maneira:
DICA: quebre a fórmula com uma fórmula IFERROR para resolver os casos em que não se encontram correspondências no conjunto de dados de pesquisa. Neste exemplo, a fórmula seria semelhante a:
Uma fórmula INDEX(MATCH()) pesquisa um intervalo e coleta o valor que corresponde aos critérios especificados. Este é o formato de uma fórmula INDEX(MATCH()):
=INDEX([Intervalo com o valor a ser retornado],MATCH([Valor da pesquisa],[Intervalo com o valor a ser pesquisado],[0, 1 ou -1 dependendo do tipo de pesquisa]))
Para extrair o preço no exemplo acima usando um INDEX(MATCH()), a fórmula ficaria assim:
=IFERROR(INDEX({Product Data | Price}, MATCH([ID de produto associado]@row, {Product Data | Product ID}, 0)), "Nenhuma correspondência encontrada")
O intervalo de referência entre planilhas de {Product Data | Price} é assim:
O intervalo de referência entre planilhas de {Product Data | Product ID} é assim:
Como observado acima, também quebramos a função INDEX(MATCH()) em um IFERROR para mostrar "Nenhuma correspondência encontrada" se nenhum ID do produto correspondente for encontrado para a linha, então a fórmula retorna os valores na coluna Preço desta maneira:
Prós:
- Permite alterações na ordenação de colunas ou exclusão de colunas não utilizadas sem quebra
- Pode extrair valores de colunas à esquerda ou à direita do intervalo de valores de pesquisa
- Mais rápido para conjuntos de dados maiores
- É possível usar INDEX(MATCH(),MATCH()) para a correspondência dinâmica de colunas e linhas
- O número total de células referenciadas é geralmente menor, o que ajuda a ficar longe do limite total de 100.000 células em referências de várias planilhas
Contras:
- Exige mais de uma referência de várias planilhas para casos em que há dados de referência em uma planilha separada
- Limitado a um único critério de correspondência
INDEX(COLLECT())
Uma fórmula INDEX(COLLECT()) pesquisa um intervalo e coleta o valor que corresponde a um ou mais critérios especificados. Este é o formato de uma fórmula INDEX(COLLECT()):
=INDEX(COLLECT([Intervalo com valor a ser retornado],[Intervalo com critério],[Critério],[Intervalo 2 com critério],[Critério] etc.),[1 para o índice de linha a ser retornado])
Para extrair o preço no exemplo acima usando um INDEX(COLLECT()), a fórmula ficaria assim:
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [ID de produto associado]@row), 1), "Nenhuma correspondência encontrada")
O intervalo de referência entre planilhas de {Product Data | Price} é assim:
O intervalo de referência entre planilhas de {Product Data | Product ID} é assim:
Como observado acima, também quebramos a função INDEX(COLLECT()) em um IFERROR para mostrar "Nenhuma correspondência encontrada" se nenhum ID do produto correspondente for encontrado para a linha, então a fórmula retorna os valores na coluna Preço desta maneira:
Prós:
- Permite alterações na ordenação de colunas ou exclusão de colunas não utilizadas sem quebra
- Pode extrair valores de colunas à esquerda ou à direita do intervalo de valores de pesquisa
- Geralmente mais rápido que o VLOOKUP, mas pode ser mais lento que o INDEX/MATCH
- Permite o uso de vários critérios na fórmula COLLECT para fazer correspondência em várias colunas ou criar critérios mais complexos.
- Permite que você informe a segunda, a terceira correspondência etc. substituindo o “, 1” no final da fórmula em vez de sempre informar a primeira correspondência
Contras:
- Exige mais de uma referência de várias planilhas para casos em que há dados de referência em uma planilha separada
- Pode ser mais lento do que usar um INDEX/MATCH, especialmente com o uso de vários critérios
Ainda precisa de ajuda?
Use o modelo Manual de fórmulas para encontrar mais recursos de suporte e conferir mais de 100 fórmulas, inclusive um glossário de cada função que você pode praticar trabalhando em tempo real e exemplos de fórmulas avançadas e de uso frequente.
Encontre exemplos de como outros clientes do Smartsheet usam essa função. Você também pode pedir ajuda sobre o seu caso específico na comunidade do Smartsheet.
Pergunte à comunidade