Você pode usar a função CORRESPONDÊNCIA do Excel para encontrar a posição relativa de um valor específico em um intervalo de células ou uma matriz.
A função CORRESPONDÊNCIA é semelhante à função VLOOKUP, pois ambas são categorizadas em Funções de consulta / referência do Excel. VLOOKUP procura um valor específico em uma coluna e retorna um valor na mesma linha, enquanto a função MATCH procura um determinado valor em um intervalo e retorna a posição desse valor.
A função MATCH do Excel procura um valor especificado em um intervalo de células ou uma matriz e retorna a posição relativa da primeira aparição desse valor no intervalo. A função MATCH também pode ser usada para pesquisar um determinado valor e retornar seu valor correspondente com a ajuda da função INDEX (assim como Vlookup). Vamos ver como usar a função MATCH do Excel para encontrar a posição de um valor de pesquisa em um intervalo de células.
Função Excel MATCH
A função MATCH é uma função interna do Excel e é usada principalmente para localizar a posição relativa de um valor de pesquisa em uma coluna ou linha.
Sintaxe da função MATCH:
= MATCH (lookup_value, lookup_array, [match_type})Onde:
lookup_value - O valor que você deseja pesquisar em um intervalo específico de células ou em uma matriz. Pode ser um valor numérico, valor de texto, valor lógico ou uma referência de célula que possui um valor.
lookup_array - As matrizes de células nas quais você está procurando um valor. Deve ser uma única coluna ou uma única linha.
tipo de partida - É um parâmetro opcional que pode ser definido como 0,1 ou -1 e o padrão é 1.
- 0 procura uma correspondência exata, quando não é encontrada retorna um erro.
- -1 procura o menor valor maior ou igual a lookup_value quando a matriz de pesquisa em ordem crescente.
- 1 procura o maior valor que é menor ou igual ao valor look_up quando a matriz de pesquisa em ordem decrescente.
Encontre a posição de uma correspondência exata
Vamos supor que temos o seguinte conjunto de dados onde queremos encontrar a posição de um determinado valor.
Nesta tabela, queremos encontrar a posição de um nome de cidade (Memphis) na coluna (A2: A23), então usamos esta fórmula:
= CORRESPONDÊNCIA ("memphis", A2: A23,0)O terceiro argumento é definido como '0' porque queremos encontrar uma correspondência exata do nome da cidade. Como você pode ver, o nome da cidade “memphis” na fórmula está em minúsculas, enquanto na tabela a primeira letra do nome da cidade está em maiúscula (Memphis). Ainda assim, a fórmula é capaz de encontrar a posição do valor especificado no intervalo fornecido. É porque a função MATCH não faz distinção entre maiúsculas e minúsculas.
Observação: Se lookup_value não for encontrado no intervalo de pesquisa ou se você especificar o intervalo de pesquisa errado, a função retornará o erro # N / A.
Você pode usar uma referência de célula no primeiro argumento da função em vez de um valor direto. A fórmula abaixo encontra a posição do valor na célula F2 e retorna o resultado na célula F3.
Encontre a posição de uma correspondência aproximada
Existem duas maneiras de procurar uma correspondência aproximada ou exata do valor de pesquisa e retornar sua posição.
- Uma maneira é encontrar o menor valor maior ou igual (a próxima maior correspondência) ao valor especificado. Isso pode ser alcançado definindo o último argumento (match_type) da função como ‘-1’
- Outra forma é o maior valor menor ou igual (próxima menor correspondência) ao valor fornecido. Isso pode ser alcançado definindo o match_type da função como ‘1’
Próxima menor correspondência
Se a função não consegue encontrar uma correspondência exata para o valor especificado quando o tipo de correspondência é definido como '1', ela localiza o maior valor que é um pouco menor do que o valor especificado (o que significa o próximo menor valor) e retorna sua posição . Para que isso funcione, você precisa classificar a matriz em ordem crescente, caso contrário, resultará em um erro.
No exemplo, usamos a fórmula abaixo para encontrar a próxima menor correspondência:
= CORRESPONDÊNCIA (F2, D2: D23,1)Quando esta fórmula não conseguiu encontrar a correspondência exata para o valor na célula F2, ela aponta para a posição (16) do próximo menor valor, ou seja, 98.
Próxima maior partida
Quando o tipo de correspondência é definido como ‘-1’ e a função MATCH não consegue encontrar uma correspondência exata, ela encontra o menor valor que é maior do que o valor especificado (o que significa o próximo maior valor) e retorna sua posição. A matriz de pesquisa deve ser classificada em ordem decrescente para este método, caso contrário, ele retornará um erro.
Por exemplo, insira a seguinte fórmula para encontrar a próxima maior correspondência para o valor de pesquisa:
= CORRESPONDÊNCIA (F2, D2: D23, -1)Esta função MATCH procura o valor em F2 (55) no intervalo de pesquisa D2: D23 e, quando não consegue encontrar a correspondência exata, retorna a posição (16) do próximo maior valor, ou seja, 58.
Correspondência de curinga
Os curingas podem ser usados na função CORRESPONDÊNCIA apenas quando match_type está definido como ‘0’ e o valor de pesquisa é uma string de texto. Existem curingas que você pode usar na função CORRESPONDÊNCIA: um asterisco (*) e um ponto de interrogação (?).
- Ponto de interrogação (?) é usado para combinar qualquer caractere ou letra com a string de texto.
- Asterisco (*) é usado para combinar qualquer número de caracteres com a string.
Por exemplo, usamos dois curingas ‘?’ Em lookup_value (Lo ?? n) da função MATCH para encontrar um valor que corresponda à string de texto com quaisquer dois caracteres (nas casas dos curingas). E a função retorna a posição relativa do valor correspondente na célula E5.
= CORRESPONDÊNCIA ("Lo ?? n", A2: A22,0)
Você pode usar o caractere curinga (*) da mesma forma que (?), Mas um asterisco é usado para corresponder a qualquer número de caracteres, enquanto um ponto de interrogação é usado para corresponder a qualquer caractere único.
Por exemplo, se você usar ‘sp *’, a função pode corresponder a alto-falante, velocidade ou spielberg, etc. Mas se a função encontrar valores múltiplos / duplicados correspondentes ao valor de pesquisa, ela retornará apenas a posição do primeiro valor.
No exemplo, inserimos “Kil * o” no argumento lookup_value. Assim, a função MATCH () procura um texto que contenha ‘Kil’ no início, ‘o’ no final e qualquer número de caracteres entre eles. ‘Kil * o’ corresponde a Kilimanjaro na matriz e, portanto, a função retorna a posição relativa de Kilimanjaro, que é 16.
INDEX e MATCH
As funções MATCH raramente são usadas sozinhas. Freqüentemente, eles se combinam com outras funções para criar fórmulas poderosas. Quando a função MATCH é combinada com a função INDEX, ela pode realizar pesquisas avançadas. Muitas pessoas ainda preferem usar PROCV para pesquisar um valor, porque é mais simples, mas INDEX MATCH é mais flexível e rápido do que PROCV.
VLOOKUP só pode pesquisar um valor verticalmente, ou seja, colunas, enquanto a combinação INDEX MATCH pode fazer pesquisas verticais e horizontais.
Função INDEX usada para recuperar um valor em um local específico em uma tabela ou intervalo. A função MATCH retorna a posição relativa de um valor em uma coluna ou linha. Quando combinado, o MATCH encontra o número da linha ou coluna (localização) de um valor específico, e a função INDEX recupera um valor baseado nesse número de linha e coluna.
Sintaxe da função INDEX:
= INDEX (matriz, núm_linha, [núm_col],)De qualquer forma, vamos ver como o INDEX MATCH funciona com um exemplo.
No exemplo abaixo, queremos recuperar a pontuação de ‘Quiz2’ da aluna ‘Anne’. Para fazer isso, usaremos a fórmula abaixo:
= ÍNDICE (B2: F20, CORRESPONDÊNCIA (H2, A2: A20,0), 3)INDEX precisa de um número de linha e coluna para recuperar um valor. Na fórmula acima, a função MATCH aninhada encontra o número da linha (posição) do valor ‘Anne’ (H2). Em seguida, fornecemos esse número de linha para a função INDEX com um intervalo B2: F20 e um número de coluna (3), que especificamos. E a função INDEX retorna a pontuação '91'.
Pesquisa bidirecional com INDEX e MATCH
Você também pode usar as funções INDEX e MATCH para pesquisar um valor em um intervalo bidimensional (pesquisa bidirecional). No exemplo acima, usamos a função MATCH para localizar o número da linha de um valor, mas inserimos o número da coluna manualmente. Mas podemos encontrar tanto a linha quanto a coluna aninhando duas funções MATCH, uma no argumento row_num e outra no argumento column_num da função INDEX.
Use esta fórmula para uma pesquisa bidirecional com INDEX e MATCH:
= ÍNDICE (A1: F20, CORRESPONDÊNCIA (H2, A2: A20,0), CORRESPONDÊNCIA (H3, A1: F1,0))Como sabemos, a função MATCH pode procurar um valor tanto horizontal quanto verticalmente. Nesta fórmula, a segunda função MATCH no argumento colum_num encontra a posição de Quiz2 (4) e a fornece para a função INDEX. E o INDEX recupera a pontuação.
Agora você sabe como usar a função Match no Excel.