Como usar a função MATCH no Planilhas Google

No Planilhas Google, o COMBINAÇÃO lhe dá a função posição relativa de um item em um intervalo de células. Basicamente, se você quiser saber a posição de um valor específico em um intervalo ou matriz, MATCH lhe dirá onde ele está localizado. MATCH é útil, mas bastante básico, mas quando você o combina com INDEX, ele se torna bastante poderoso. O Função INDEX MATCH é uma alternativa brilhante e mais dinâmica à função VLOOKUP. Ela resolve muitos dos problemas associados ao VLOOKUP.

COMBINAÇÃO

Primeiramente, vamos dar uma olhada na função MATCH e como ela funciona no Planilhas Google.

Lembre-se de que MATCH retorna um posição mas não um valor, portanto, se você quiser retornar um valor propriamente dito, use ÍNDICE, HLOOKUP, ou VLOOKUP.

No instantâneo abaixo, você pode ver que a posição de Evan é 5 dentro do intervalo de células A1 a A6.

E se você colocar as células como mostrado no instantâneo abaixo (B4 a B9)?

A posição relativa de Evan ainda permanece 5.

É exatamente isso que a função MATCH foi projetada para fazer: retornar a posição relativa de um item (‘Evan’) em um intervalo de células (A1:A6 ou B4:B9).

Sintaxe

• chave_de_busca - é o item que a função MATCH procura dentro do alcance de células. Pode ser um texto puro (‘Evan’), ou uma referência de célula (como A7), ou mesmo uma função que retorna uma cadeia de caracteres ou um número (como LEFT(“Mike Johnson”,8) ou DATE(2017,1,1))
• alcance - é o grupo de células em que a função MATCH procura o item (search_key). Essa deve ser uma matriz unidimensional, ou seja, um intervalo com uma única coluna ou uma única linha.
• tipo de pesquisa - é uma entrada opcional que orienta como a função MATCH deve procurar o chave_de_busca no alcance. Isso assume três valores diferentes:
  • 1 é o valor padrão (ou seja, quando nenhuma entrada é fornecida em relação a tipo de pesquisa). Com essa opção, o Planilhas Google presume que o intervalo de células está classificado em ordem crescente e, portanto, retorna o maior valor menor ou igual a chave_de_busca.
  • 0 especifica que o Google Sheets deve encontrar uma correspondência exata. Essa é a opção ideal a ser usada se o alcance de células não é classificado em nenhuma ordem.
  • -1, Como se poderia imaginar, é exatamente o oposto de 1. Essa opção pressupõe que o intervalo de células esteja classificado em ordem decrescente e retorna o menor valor maior ou igual a chave_de_busca.

Como usar o MATCH no Planilhas Google

Dê uma olhada na captura de tela abaixo. Na coluna A, tenho um conjunto de dados em ordem crescente (por exemplo, do menor para o maior). Experimentei algumas variações da função MATCH nesses dados.

No primeiro exemplo, na linha 2, solicitei que a função MATCH procurasse o ID #1400 digitando =Match(1400,A2:A15,1).

A segunda fórmula, na linha 3, faz o mesmo que a primeira, e ambas me dizem que o ID #1400 está na 7ª linha.

A diferença é que, no primeiro exemplo, usei o search_type 1, que é usado quando os dados estão em ordem crescente (o que é o caso).

Na segunda vez, usei search_type 0, que é usado quando os dados não estão classificados ou quando você deseja apenas uma correspondência exata. Como os dados estavam classificados e havia uma correspondência exata para 1400, ambas as funções me deram o mesmo resultado.

No terceiro exemplo, na linha 4, peço à função MATCH que encontre o ID #1300 digitando =Match(1300,A2:A15,0). Uso search_type 0 para especificar que quero uma correspondência exata. Como não existe o ID #1300, recebo um erro.

No quarto exemplo na linha 5, entretanto, pergunto a mesma coisa, mas uso search_type 1. Aqui, a função me dá um resultado, indicando que a resposta está na linha 4.

Observação sobre MATCH

Um aspecto a ser lembrado é que a função MATCH não necessariamente fornecerá a resposta mais próxima - como você pode ver, a linha 4 corresponde ao ID #1125.

A próxima linha, ID #1313, está muito mais próxima do ID #1300 que eu estava solicitando. No entanto, ao pesquisar dados em ordem crescente, a função retorna o valor mais próximo que não seja maior que a search_key. Por esse motivo, ela retorna a linha 4 como resultado, e não a linha 5.

No último par de exemplos, nas linhas 6 e 7, em vez de digitar o chave_de_busca diretamente, eu indico uma célula de referência que contém o chave_de_busca. Eu faço isso com a função =Match(D2,A2:A15,1).

No próximo exemplo, mostrarei os dados classificados em ordem decrescente.

Os exemplos aqui são os mesmos da primeira imagem, exceto que agora estou usando um search_key de -1 em vez de 1 para refletir o fato de que os dados são classificados em ordem decrescente.

Observe o exemplo da busca pelo ID #1300 - dessa vez, a função fornece um resultado da linha 10, que corresponde ao ID #1313, muito mais próximo de 1300 do que quando a mesma coisa foi feita com os dados classificados em ordem crescente.

Usando MATCH com texto

Os exemplos mostrados foram com valores numéricos. Que tal usar a função MATCH com valores de texto?

Como não é possível definir exatamente os valores ‘menor que’ e ‘maior que’ para formulários de texto, geralmente uso a opção search_type 0, que diz ao Planilhas Google para procurar uma correspondência exata. Aqui estão alguns exemplos:

Observe como o chave_de_busca precisa estar entre aspas quando for uma cadeia de texto, como no primeiro exemplo: =Match(“Eric”,A2:A14,0). Outro aspecto a ser lembrado é que, ao usar o search_key 0 para procurar uma correspondência exata; se não houver correspondência exata, a função apresentará um erro (como no primeiro exemplo acima).

Tenho usado a função MATCH em um layout vertical porque é o mais comum. Mas certamente há ocasiões em que você pode querer usá-la em um conjunto de dados horizontal. No exemplo abaixo, fiz exatamente isso.

Aqui, a função MATCH informa o número da coluna que contém o chave_de_busca.

Como usar a combinação de funções INDEX MATCH

Talvez o uso mais poderoso da função MATCH no Planilhas Google seja quando você a utiliza em combinação com a função INDEX, para procurar valores. Mas já existe uma função VLOOKUP no Planilhas Google para essa finalidade, certo?

É verdade, mas a combinação de MATCH e INDEX pode resolver vários problemas que surgem quando se usa VLOOKUP.

Dê uma olhada no exemplo de VLOOKUP abaixo:

A função VLOOKUP em D2 procura 161 na coluna ‘Emp ID #’ (por ser a mais à esquerda no intervalo A2:B11) e, a partir da linha em que encontra o valor 161, busca o valor localizado na segunda coluna (ou seja, a coluna B), assumindo que os dados não estão classificados. Até aqui, tudo bem. Mas há dois problemas críticos ao usar o VLOOKUP no Planilhas Google.

Problema 1: Referência a células estáticas

O que acontece quando você insere uma nova coluna entre a primeira e a segunda coluna? Vamos tentar isso.

Você notará que o valor retornado não é Etienne mais. Isso ocorre porque o VLOOKUP é uma função semi-estática.

O Planilhas Google atualizou o segundo parâmetro para refletir o novo intervalo, mas não alterou adequadamente o índice da coluna (terceiro parâmetro) quando uma nova coluna foi adicionada antes do Vendedor coluna.

Problema 2: A coluna de pesquisa é sempre a mais à esquerda

Talvez você se encontre em uma situação em que precise procurar valores de uma coluna (Emp ID #) que não seja a mais à esquerda, como mostrado abaixo.

Nesse caso, mover a coluna Emp ID # para torná-la a mais à esquerda funcionaria. Mas essa não é a abordagem ideal - pode haver especificações de layout de dados ou de apresentação que não permitam que você reorganize as colunas. Nessa situação, o que você pode fazer? A combinação das funções MATCH e INDEX vem em seu socorro. Aqui está a sintaxe da combinação, seguida de alguns exemplos.

INDEX(reference, MATCH(search_key, range, search_type))

A chave para essa combinação é que ambos os intervalos selecionados para as funções INDEX e MATCH, respectivamente, precisam ser uma única coluna. Basicamente, você está usando uma função VLOOKUP, mas especificando a coluna para procurar e a coluna para retornar o valor, em intervalos separados dentro das funções MATCH e INDEX. Como você verá, essa metodologia o ajuda a evitar os erros que podem ocorrer com a função PROCV.

No exemplo acima, usei a função MATCH para me informar qual linha contém o ID de funcionário #161 na coluna C. A função retorna um número que informa à função INDEX qual linha da coluna A deve procurar o vendedor. O resultado é que as duas funções se combinam para me informar o nome correto (Ethan) do ID de funcionário #161. A função que usei para fazer isso é =INDEX(A2:A11,MATCH(161,C2:C11,0)).

Como você pode ver, ao contrário do PROCV, a combinação funciona mesmo que a coluna de pesquisa não seja a mais à esquerda (veja os três primeiros exemplos na captura de tela acima). Não é de surpreender que ela também funcione como o VLOOKUP, quando a coluna de pesquisa é a mais à esquerda.

Vamos ver também se as funções se mantêm quando você introduz uma nova coluna no meio.

Felizmente, elas ainda funcionam. Assim que introduzi uma nova coluna (Estado), o Planilhas Google atualizou as referências automaticamente para acomodar essa alteração.

Como você viu acima, a combinação das funções MATCH e INDEX é muito mais flexível e versátil do que a já popular e poderosa função VLOOKUP. Mas a combinação dessas funções pode ser ainda mais poderosa quando você usa duas funções MATCH em vez de apenas uma.

Como usar a função INDEX com 2 funções MATCH

Você já viu como a combinação INDEX e MATCH pode ser útil. Mas e se você tiver uma matriz bidimensional da qual deseja obter um valor? Nesse caso, você pode usar a função MATCH duas vezes dentro da função INDEX, conforme mostrado no exemplo abaixo.

Neste exemplo, você pode ver dados sobre a temperatura média em cada mês, para os anos de 2015 a 2020. Se você quiser extrair a temperatura média de um mês específico de um ano específico dessa matriz, poderá usar a combinação INDEX e MATCH para fazer isso. A função usada aqui é:
=INDEX(B2:M7, Match(B11, A2:A7, false), Match(B10, B1:M1, false))

Como você pode ver, estou usando a função MATCH para me fornecer a localização da linha do ano que estou procurando (neste caso, 2018, que está na 4ª linha de dados), bem como a coluna do mês que estou procurando (setembro, na 9ª coluna de dados). A função INDEX usa essas coordenadas e retorna a temperatura média de setembro de 2018.

Ao usar as funções INDEX e MATCH, você pode não apenas obter uma função de pesquisa mais versátil do que a VLOOKUP, mas também pode pesquisar matrizes bidimensionais. Para saber mais sobre a função INDEX e seu uso, confira a seguinte postagem do blog: Como usar a função INDEX no Planilhas Google.