Como reverter vlookup no excel com index-match?

Para entender como INDEX e MATCH funcionam como uma fórmula de pesquisa reversa, você precisa escolher uma coisa simples: Match informa ao índice a posição (número da célula) de um valor em uma coluna ou linha, então o índice retorna esse valor. valor usando esta posição (número da célula) .

Pense assim: a função MATCH é um agente disfarçado que encontra o criminoso e a função INDEX é um policial que posteriormente prende o criminoso.

Mas vamos aprender detalhadamente como podemos combinar essas duas funções. Abaixo está a sintaxe do INDEX como você sabe.

ÍNDICE(matriz, núm_linha, [núm_coluna])

Na função INDEX, o argumento row_num informa de qual linha o valor deve ser retornado. Digamos que se você inserir 4, retornará o valor da 4ª linha.

Para criar a fórmula de pesquisa reversa, precisamos substituir MATCH por row_argument de INDEX.

Quando usamos MATCH, ele procura o valor na coluna de pesquisa e retorna o número da célula desse valor. E então, INDEX usa esse número para determinar a posição da célula na coluna de valor.

No final ele retorna o valor daquela célula e você obtém o valor que procura. Mas agora vamos trabalhar com um exemplo real. Abaixo temos uma lista de cidades e os nomes dos funcionários que trabalham lá.

Aqui precisamos procurar o nome do funcionário que trabalha em Mumbai. Agora, se você olhar os dados, na coluna onde você tem cidades, essa é a nossa coluna de pesquisa, e na coluna onde você tem os nomes dos funcionários, essa é a sua coluna de valor. E a fórmula será:

 =INDEX(B2:B11,MATCH(E3,A2:A11,0),0)

Vamos dividir essa fórmula em duas partes para entendê-la.

Parte 1 : Na primeira parte utilizamos a função match para buscar o valor “Mumbai” e ela retornou o “5” que é a posição da célula em que está o valor “Mumbai” na coluna de cidades.

Parte 2 : Na segunda parte, usamos INDEX e nos referimos à coluna do nome do funcionário para encontrar o valor. Aqui a função de índice sabe que você deseja o valor da 5ª célula da coluna. Então, retornou “Siya” no resultado.

Mais exemplos de INDEX e correspondência

Temos diante de nós alguns dos problemas comuns que resolvemos usando a fórmula INDEX MATCH. Acesse os arquivos de amostra: certifique-se de baixar esses arquivos de amostra aqui para acompanhar cada exemplo.

1. Pesquisa básica com INDEX – MATCH

Uma pesquisa normal é uma das tarefas mais importantes que você precisa realizar com fórmulas de pesquisa e INDEX MATCH é perfeito para isso. Aqui temos uma tabela de dados com ID e nome do funcionário. Cada ID é único e você precisa pesquisar o nome do funcionário junto com seu ID.

Digamos que você queira pesquisar o nome EMP-132. Para isso a fórmula será:

 =INDEX(name_column,MATCH(emp-id,emp-id_column,0))

é assim que esta fórmula funciona

Primeiro , MATCH corresponde ao emp id na coluna emp id e retorna o número da célula do id que você está procurando. Aqui o número da linha é 6.

Depois disso, INDEX retorna o nome do funcionário da coluna de nome usando o mesmo número de célula.

2. Olhe para a esquerda

VLOOKUP não pode ir para a esquerda ao procurar um valor. Como mencionei, em INDEX e MATCH você pode pesquisar em qualquer direção. Na tabela de dados abaixo você tem a coluna do número da fatura após a coluna do valor.

Portanto, se você deseja pesquisar o valor de uma determinada fatura, isso não é possível com o VLOOKUP. Em VLOOKUP, quando você seleciona uma tabela, a primeira coluna dessa tabela será a coluna de pesquisa.

Mas, aqui nesta tabela, precisamos usar a última coluna da tabela como coluna de pesquisa. Portanto, não gostei do VLOOKUP aqui. Vamos chamar INDEX e MATCH para resgate e a fórmula será:

 =INDEX(G2:G14,MATCH(L6,J2:J14,0),0)

… é assim que esta fórmula funciona

• Primeiro de tudo , você se referiu à coluna de valor na função de índice. Esta é a coluna da qual precisamos obter o valor.
• Em segundo lugar , no argumento row_number da função de índice, você usou a função de correspondência e especificou o número da fatura, referiu-se à coluna da fatura e usou zero para correspondência exata.
• Terceiro , a função de correspondência retorna o número da célula da fatura do intervalo.

E no final, INDEX usa esse número para retornar o valor posicionando a célula da coluna de valor.

3. Pesquisa difusa

Assim como VLOOKUP, você também pode usar INDEX/MATCH para uma pesquisa aproximada.

A pesquisa difusa pode ser útil quando o valor que você está procurando não está listado e você deseja obter a correspondência mais próxima. Na tabela abaixo você tem uma lista de notas com base nas notas.

E, se quiser obter uma pontuação de 79, você pode usar a fórmula abaixo.

 =INDEX(B2:B6,MATCH(D3,A2:A6,1))

… é assim que esta fórmula funciona

Nesta fórmula, usamos 1 na função de correspondência para match_type que permite realizar uma pesquisa aproximada. Ele retorna o primeiro valor menor ou igual ao valor pesquisado.

Para 79, o primeiro valor mais baixo é 75, e para 75, a nota é B. É por isso que você obtém B no resultado.

4. PESQUISA Horizontal

Como você sabe, HLOOKUP é para pesquisa horizontal, mas você também pode usar INDEX e MATCH para isso. Aqui na tabela de dados abaixo você tem uma tabela horizontal de vendas mensais e deseja obter o valor das vendas de “maio”.

E a fórmula será:

 =INDEX(amount,0,MATCH(lookup_month,months,0))

… é assim que esta fórmula funciona

Na fórmula acima, em vez de usar MATCH no argumento row_num do índice, usamos em column_num. E match retorna o número da coluna de maio.

E então INDEX retorna o valor da coluna de resultado com base no número da posição.

5. Pesquisa bidirecional

Numa pesquisa bidirecional, precisamos obter um valor de uma tabela. Basta olhar a tabela abaixo onde você tem os valores de vendas por área e por produto.

Agora se você deseja obter o valor das vendas de um produto para uma determinada área você precisa de uma busca bidirecional e para isso você precisa usar uma combinação de INDEX MATCH MATCH. Sim, aqui você tem que usar MATCH duas vezes.

Em uma combinação normal de INDEX e MATCH, você usa MATCH para o número da linha, mas em uma pesquisa bidirecional você também deve usá-lo para o número da coluna. As fórmulas serão:

 =INDEX(A1:F5,MATCH(H3,A1:F1,0),MATCH(H2,A1:A5,0))

… é assim que esta fórmula funciona

Na fórmula acima, para obter o valor das vendas da tabela, você usou a função de índice e, em seguida, a função de correspondência para seus argumentos núm_linha e núm_coluna. A função de correspondência que é o argumento column_num retorna 5 como o valor do Produto-D na 5ª linha do intervalo que você referenciou.

E a função de correspondência encontrada no argumento row_num retorna 2 porque o valor da zona norte está na segunda coluna do intervalo que você referenciou.

Agora, com esses valores, a função de índice retornou o valor que está na 2ª coluna e na 5ª linha: 1456.

6. Diferencia maiúsculas de minúsculas

Se você encontrar um problema ao ter dois valores iguais em uma lista ou coluna, mas em letras maiúsculas e minúsculas, você pode fazer uma pesquisa com distinção entre maiúsculas e minúsculas para encontrar o valor correto. Vamos dar uma olhada na lista de alunos abaixo onde você tem o primeiro nome e na segunda coluna você marcou.

E, no início, há substantivos que são iguais, mas em casos de texto diferentes. Por exemplo, John Parker e JOHN Mathew. Digamos que você queira pesquisar as marcas “JOHN” e não “John”, você pode criar uma pesquisa de correspondência exata com INDEX e MATCH. E a fórmula será:

 =INDEX(B2:B21,MATCH(TRUE,EXACT(E5,A2:A21),0))

… é assim que esta fórmula funciona

Aqui nesta fórmula você usou a função EXATA na função de correspondência. Como a função match não é capaz de procurar um valor que diferencia maiúsculas de minúsculas e EXACT é uma função perfeita para isso.

Ele pode comparar dois valores e retornar TRUE se eles forem exatamente iguais (incluindo maiúsculas e minúsculas), mas você deve inserir esta fórmula em forma tabular porque precisa comparar a coluna inteira com um único valor em EXATO. Ao inseri-lo, ele retornará um array como este.

 =INDEX(Sheet1!$B$2:$B$21,MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},0))

A partir daí você precisa usar a função match para obter a posição TRUE do array.

Neste ponto você tem o número da célula (número da linha) do valor que precisa encontrar. E você pode usar INDEX para este valor de coluna.

Ponto importante: se você obtiver mais de um VERDADEIRO com EXATO, a correspondência simplesmente retornará o número do primeiro VERDADEIRO

7. Com curingas com INDEX MATCH

Os curingas são muito úteis. Você pode realizar uma pesquisa parcial usando curingas . E a melhor parte é que, como todas as outras pesquisas de fórmulas, você também pode usar curingas com índice e correspondência.

Basta dar uma olhada na lista de nomes abaixo, onde você encontra o nome e o sobrenome dos funcionários e suas idades.

A partir desta lista você precisa obter a idade de um determinado funcionário (Sondra). Mas o fato é que você só conhece o primeiro nome.

E, se você usar um asterisco, poderá pesquisar a idade de Sondra usando o primeiro nome. Para isso a fórmula será:

 =INDEX(B2:B11,MATCH("Sondra*",A2:A11,0),0)

… é assim que esta fórmula funciona

Um asterisco é um caractere curinga que pode substituir um número n de caracteres. Então, quando você usou depois do primeiro nome, ele substituiu o sobrenome.

8. Menor valor

Digamos que você tenha uma lista de alunos com suas pontuações, como abaixo. E agora nesta lista você deseja pesquisar o nome do aluno que tem a nota mais baixa.

Para isso, você pode usar a função MIN com índice e correspondência e a fórmula será:

 =INDEX(A2:A21,MATCH(MIN(B2:B21),B2:B21,0))

Você tem os nomes dos alunos na coluna A e suas pontuações na coluna B.

Portanto, ao inserir esta fórmula em uma célula e pressionar Enter, ela retornará o nome do aluno com menor pontuação, ou seja, Librada Bastian.

Explicação

Nesta fórmula temos três partes diferentes.

Na primeira parte, a função MIN retorna a pontuação mais baixa.

Depois disso, na segunda parte, a função match retorna a célula com a pontuação mais baixa.

No final, a função de índice retorna o valor da coluna do nome do aluno usando a mesma posição da célula retornada pela correspondência.

Dica: Da mesma forma, você também pode obter o nome do aluno que obteve a maior pontuação.

9. Melhor enésima pontuação

Agora pense assim, você tem uma lista de alunos com suas notas nos exames e dessa lista deseja obter o nome do aluno que obteve a 2ª maior nota.

O problema é que você não sabe qual é a segunda pontuação mais alta.

Normalmente, ao pesquisar um valor com fórmulas de pesquisa, você tem certeza do valor que procura. Mas aqui você não sabe qual é a segunda pontuação mais alta.

Então, para isso você pode combinar uma função grande com um índice e combiná-lo. A função grande o ajudará a determinar o segundo valor mais alto do intervalo.

E a fórmula será:

 =INDEX(student_names,MATCH(LARGE(score,2),score,0))

… é assim que esta fórmula funciona

Nesta fórmula, você usou a função grande dentro da função de correspondência para o argumento lookup_value. E na grande função você mencionou o intervalo de pontuação e 2 para obter o segundo valor mais alto.

Depois que a função grande retorna o segundo maior valor, a função match usa esse valor e retorna o número da célula correspondente.

E no final, a função index usa esse número de célula e retorna o nome do aluno.

10. Múltiplos critérios

Normalmente a combinação de índice e correspondência tem como objetivo procurar um único valor. E é por isso que você usa apenas um intervalo na função match.

Mas, às vezes, quando você se depara com dados do mundo real, precisa usar vários critérios para encontrar um valor.

Considere o exemplo abaixo. Aqui você tem uma lista de produtos com diversos detalhes como nome do produto, categoria e tamanho.

E a partir desses dados você deseja obter o preço de um determinado produto utilizando todos os critérios.

A fórmula será portanto:

 =INDEX(D2:D11,MATCH(1,(A14=A2:A11)*(B14=B2:B11)*(C14=C2:C11),0))

Nota: Esta é uma fórmula de matriz, então você precisa inseri-la usando ctrl + shift + enter.

… é assim que esta fórmula funciona

Nesta fórmula você tem três arrays diferentes para corresponder a três valores diferentes e esses arrays retornam TRUE e FALSE onde os valores coincidem.

Depois disso, quando você os multiplica, você obtém um array ou algo parecido.

 =INDEX(D2:D11,MATCH(1,{0;0;0;0;0;0;1;0;0;0},0))

E a função match retorna a posição 1 para o array.

No final, INDEX retorna o preço da coluna de preço usando o número retornado pela correspondência.

Dica: se não quiser usar uma fórmula de matriz, você pode usar a condição SUMPRODUCT .

11. Primeiro valor numérico de um intervalo

Digamos que você tenha uma lista na qual possui valores de texto e numéricos e agora, dessa lista, você deseja obter o primeiro valor numérico.

Para fazer isso, você pode combinar a função ISNUMBER com índice/correspondência. ISNUMBER pode ajudá-lo a identificar qual valor é um número e qual é texto.

A fórmula será:

 =INDEX(list,MATCH(TRUE,ISNUMBER(list),0))

Você deve inserir esta fórmula em forma de tabela (usando Ctrl + Shift + Enter).

… é assim que esta fórmula funciona

Nesta fórmula ISNUMBER retorna um array igual ao comprimento da lista e neste array você tem TRUE para os valores que são números e FALSE para o restante dos valores.

Depois disso, você usou TRUE na função de correspondência como valor de pesquisa. Portanto, ele retorna o número da posição do primeiro TRUE no array.

Em última análise, usar este índice de número de posição retorna o primeiro valor numérico.

12. Obtenha o primeiro valor não vazio

Vamos pensar assim, você tem uma lista de valores onde algumas das primeiras células estão vazias e deseja obter o primeiro valor não vazio.

E você pode usar esta fórmula para obter esse primeiro valor não vazio.

 =INDEX(A2:A11,MATCH(FALSE,ISBLANK(A2:A11),0))

… é assim que esta fórmula funciona

Precisamos dividir esta fórmula em três partes diferentes para entendê-la com sabedoria.

Primeiro , você usou a função ISBLANK na função match para obter um array onde você tem TRUE para células vazias e FALSE para células não vazias.

Segundo , MATCH retorna o número da posição do primeiro TRUE na matriz retornada por ISBLANK.

Portanto, neste ponto você tem o número da célula do primeiro valor não vazio.

Terceiro , a função de índice simplesmente retorna o primeiro valor não vazio da lista.

13. Texto mais frequente

Agora suponha que dada uma lista de valores de texto, você precise contar o texto mais frequente.

Na lista abaixo você tem nomes.

Mas, existem alguns nomes que são mais de uma vez.

Então, agora você precisa obter o nome que tem a maior ocorrência na lista. Você pode usar a fórmula abaixo, que é uma combinação de MODE, INDEX e MATCH.

 =INDEX(A2:A13,MODE(MATCH(A2:A13,A2:A13,0)))

Ao inserir esta fórmula ela retornará “Tamesha” que é o nome mais comum.

… é assim que esta fórmula funciona

Primeiro , MATCH irá comparar todo o intervalo de nomes consigo mesmo. E, ao fazer isso, retornará um array onde cada texto representará sua primeira posição.

Vejamos um exemplo do nome “Tamesha”, que é o nosso nome mais comum na lista. Agora , se você olhar a lista, isso aconteceu primeiro na 8ª célula e depois na 12ª célula.

Mas, se você olhar a tabela, para todas as posições onde temos “Tamesha”, ela retornou 8 que é a sua primeira posição.

Depois disso, a partir do array retornado pelo MATCH, a função mode retornará o número mais frequente que é o número da célula da primeira ocorrência de “ Tamesha ”.

E no final, INDEX retornará o texto usando este número de célula.

14. Crie um hiperlink

Agora, digamos que além de buscar um valor, você também queira criar um hiperlink para esse valor. Dessa forma, você pode navegar rapidamente até a célula onde sua coluna de pesquisa está localizada.

Por exemplo, na tabela abaixo você precisa saber a idade de uma pessoa. E, se você criar um hiperlink para esse valor, poderá navegar facilmente até a célula onde esse valor está localizado.

E, para isso devemos utilizar HYPERLINK + Cell com INDEX e MATCH e a fórmula será:

É assim que esta fórmula funciona

Vamos separar esta fórmula em várias partes para entendê-la melhor.

• Primeiro de tudo, você usou índice e correspondência na função de célula. E, ao usar ambas as funções na função de célula, você obtém uma referência de célula do valor correspondente em vez do valor correspondente.
• Segundo , você concatenou “#” com a referência da célula.
• Terceiro , você usou index e match novamente para obter o valor correspondente para usar como texto do link. Desta forma você tem o valor correspondente, bem como o link para a célula onde esse valor está localizado.