Este tutorial irá ensiná-lo a usar a combinação INDEX & MATCH para realizar pesquisas no Excel e no Google Sheets.
INDEX & MATCH, o par perfeito
Vamos dar uma olhada em algumas das maneiras de combinar as funções INDEX e MATCH. A função MATCH é projetada para retornar a posição relativa de um item dentro de uma matriz, enquanto a função INDEX pode buscar um item de uma matriz dada uma posição específica. Essa sinergia entre os dois permite que eles realizem quase qualquer tipo de pesquisa de que você possa precisar.
A combinação INDEX / MATCH tem sido usada historicamente como um substituto para a função VLOOKUP. Um dos principais motivos é a capacidade de fazer uma pesquisa voltada para a esquerda (consulte a próxima seção).
Observação: a nova função XLOOKUP agora pode realizar pesquisas voltadas para a esquerda.
Pesquisa à Esquerda
Vamos usar esta tabela de estatísticas de basquete:
Queremos encontrar Bob’s Player #. Como o Player # está à esquerda da coluna do nome, não podemos usar VLOOKUP.
Em vez disso, poderíamos fazer uma solicitação MATCH básica para calcular a linha de Bob
= CORRESPONDÊNCIA (H2, B2: B5, 0)
Isso procurará uma correspondência exata da palavra "Bob" e, portanto, nossa função retornaria o número 2, já que "Bob" está no 2WL posição.
Em seguida, podemos usar a função INDEX para retornar o Player #, correspondente a uma linha. Por enquanto, vamos inserir manualmente "2" na função:
= ÍNDICE (A2: A5, 2)
Aqui, INDEX fará referência a A3, uma vez que é o 2WL célula dentro do intervalo A2: A5 e retornar o resultado de 42. Para nosso objetivo geral, podemos combinar esses dois em:
= ÍNDICE (A2: A5, CORRESPONDÊNCIA (H2, B2: B5, 0))
A vantagem aqui é que pudemos retornar um resultado de uma coluna à esquerda de onde estávamos pesquisando.
Pesquisa bidimensional
Vejamos nossa mesa anterior:
Desta vez, entretanto, queremos buscar uma estatística específica. Dissemos que queremos pesquisar Rebounds na célula H1. Em vez de ter que escrever várias instruções IF para determinar de qual coluna obter o resultado, você pode usar uma função MATCH novamente. A função INDEX permite que você especifique o valor da linha e o valor da coluna. Vamos adicionar outra função MATCH aqui para determinar qual coluna queremos. Isso vai se parecer com
= CORRESPONDÊNCIA (H1, A1: E1, 0)
Nossa célula em H1 é um menu suspenso que nos permite escolher a categoria que queremos pesquisar e, em seguida, nosso MATCH determina a qual coluna da tabela pertence. Vamos inserir essa nova parte em nossa fórmula anterior. Observe que precisamos ajustar o primeiro argumento para ter duas dimensões, já que não queremos mais apenas um resultado da coluna A.
= ÍNDICE (A2: E5, CORRESPONDÊNCIA (H2, B2: B5, 0), CORRESPONDÊNCIA (H1, A1: E1, 0))
Em nosso exemplo, queremos encontrar Rebounds for Charlie. Nossa fórmula vai avaliar isso da seguinte forma:
= INDEX (A2: E5, MATCH ("Charlie", B2: B5, 0), MATCH ("Rebounds", A1: E1, 0)) = INDEX (A2: E5, 3, 4) = D4 = 6
Agora criamos uma configuração flexível que permite ao usuário buscar qualquer valor que desejar em nossa tabela, sem ter que escrever várias fórmulas ou instruções IF de ramificação.
Múltiplas seções
Não é frequentemente usado, mas INDEX tem um quinto argumento que pode ser dado para determinar qual área dentro do argumento um para usar. Isso significa que precisamos de uma maneira de passar várias áreas para o primeiro argumento. Você pode fazer isso usando um conjunto extra de parênteses. Este exemplo ilustrará como você pode obter resultados de diferentes tabelas em uma planilha usando INDEX.
Este é o layout que usaremos. Temos estatísticas para três trimestres diferentes de jogo.
Nas células H1: H3, criamos listas suspensas de validação de dados para nossas várias opções. A lista suspensa para o trimestre vem de J2: J4. Usaremos isso para outra instrução MATCH, para determinar qual área usar. Nossa fórmula em H4 será semelhante a esta:
= ÍNDICE ((A3: E6, A10: E13, A17: E20), CORRESPONDÊNCIA (H2, B3: B6, 0), CORRESPONDÊNCIA (H1, A2: E2, 0), CORRESPONDÊNCIA (H3, J2: J4, 0))
Já discutimos como funcionam as duas funções MATCH internas, então vamos nos concentrar no primeiro e no último argumento:
= ÍNDICE ((A3: E6, A10: E13, A17: E20), …, CORRESPONDÊNCIA (H3, J2: J4, 0))
Demos à função INDEX vários arrays no primeiro argumento, colocando-os todos entre parênteses. A outra maneira de fazer isso é usando Fórmulas - Definir nome. Você pode definir um nome chamado “MyTables” com uma definição de
= INDEX (MyTable, MATCH (H2, Table1347 [Name], 0), MATCH (H1, Table1347 [#Headers], 0), MATCH (H3, J2: J4,0))
Vamos voltar a toda a declaração. Nossas várias funções MATCH dirão à função INDEX exatamente onde olhar. Primeiro, vamos determinar que “Charlie” é o 3rd fileira. Em seguida, queremos "rebotes", que é o 4º coluna. Finalmente, determinamos que queremos o resultado de 2WL tabela. A fórmula será avaliada da seguinte forma:
= ÍNDICE ((A3: E6, A10: E13, A17: E20), CORRESPONDÊNCIA (H2, B3: B6, 0), CORRESPONDÊNCIA (H1, A2: E2, 0), CORRESPONDÊNCIA (H3, J2: J4, 0)) = ÍNDICE ((A3: E6, A10: E13, A17: E20), 3, 4, 2) = ÍNDICE (A10: E13, 3, 4) = D13 = 14
Como mencionamos no início deste exemplo, você está limitado a ter as tabelas na mesma planilha. Se você puder escrever maneiras corretas de dizer ao seu INDEX de qual linha, coluna e / ou área deseja recuperar os dados, o INDEX o atenderá muito bem.
Planilhas Google -ÍNDICE E CORRESPONDÊNCIA
Todos os exemplos acima funcionam exatamente da mesma forma no Planilhas Google e no Excel.
