Como usar a função INDEX no Planilhas Google

O Função INDEX no Planilhas Google retorna o valor de uma célula em um intervalo de entrada, relativamente separado da primeira célula por deslocamentos de linha e coluna. Isso é semelhante ao índice no final de um livro, que oferece uma maneira rápida de localizar um conteúdo específico.

Sintaxe

INDEX(reference, [row_offset], [column_offset])

• referência - o endereço do intervalo de células dentro do qual o deslocamento é avaliado a partir da primeira célula (no canto superior esquerdo). Dessa forma, a fórmula INDEX retorna o valor da célula de destino do deslocamento.
• row_offset - o número de linhas a serem deslocadas da célula inicial.
• column_offset - o número de colunas a serem deslocadas da célula inicial.

Como usar a função INDEX no Planilhas Google

Vamos dar uma olhada em alguns exemplos práticos para entender como usar essa função.

Nos exemplos a seguir, usei alguns dados fictícios para mostrar a função INDEX em uso.

Na captura de tela acima, todos os casos, exceto o último, são bastante simples.

Basicamente, você fornece à fórmula um intervalo de células e, em seguida, fornece a ela as coordenadas da célula no intervalo que você deseja que ela retorne como resultado. Assim, em linha 3, onde a fórmula é =INDEX(A2:B11,8,2), estou dizendo à fórmula que quero a célula na linha 8, coluna 2 da matriz, que é $13,947.

No último exemplo da captura de tela (linha 5) Não especifiquei nenhum deslocamento de linha ou coluna. Como resultado, a função retornou o conjunto completo de valores do intervalo de referência de entrada. No mínimo, isso poderia servir como entrada para outra fórmula de matriz. Por exemplo, você poderia usar isso para consolidar dados de várias planilhas ou poderia fazer algo como =COUNTA(INDEX(A2:A8)), que teria retornado o valor 7.

Há algum caso em que a função INDEX pode gerar um erro? Claro que sim! Quando você tenta apontar a função para fora dos limites do intervalo de entrada, ela apresenta um erro, conforme mostrado na captura de tela abaixo:

Como usar a função INDEX em combinação com outras fórmulas

Aqui, darei uma olhada em alguns casos de uso que demonstram como a função INDEX do Google Sheets pode ser usada em combinação com outras funções e fórmulas.

Caso de uso #1: função INDEX e função COUNTA

A combinação da função COUNTA com a função INDEX pode ser útil em situações em que, por exemplo, você sempre deseja realizar um cálculo usando a função última linha de dados em uma lista que está sendo regularmente ampliado.

Na captura de tela a seguir, tenho uma planilha que é atualizada toda semana com a temperatura média da semana.

Se eu quiser sempre realizar um cálculo com a temperatura média da semana mais recente, posso usar COUNTA e INDEX para selecionar sempre a última entrada, usando a fórmula =INDEX((A:B), COUNTA(A:A),2)

Aqui, as colunas A e B inteiras são o intervalo. A função COUNTA(A:A) conta o número de pontos de dados na coluna A e informa quantos são, e a fórmula INDEX considera esse número como a linha da qual obterá o resultado.

O 2 no final informa à fórmula que ela obterá o resultado da segunda coluna do intervalo (coluna B). Portanto, não importa quantas semanas você adicione à lista, a “Semana mais recente” sempre estará atualizada!

Caso de uso #2: combinação das funções MATCH e INDEX

Talvez um dos usos mais poderosos da função INDEX no Planilhas Google seja quando ela é usada junto com a função MATCH para procurar valores.

Mas já existe uma fórmula VLOOKUP no Planilhas Google para essa finalidade, certo?

Dê uma olhada no exemplo abaixo:

A fórmula VLOOKUP em D2 procura 161 na tabela Emp ID # (pois é a mais à esquerda no intervalo A2:B11) e, a partir da linha em que encontra o valor 161, ele 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 com o VLOOKUP no Planilhas Google.

Problemas com VLOOKUP que podem ser resolvidos usando as funções INDEX e MATCH

Antes de prosseguir com exemplos de como usar as funções INDEX e MATCH em combinação, descreverei dois problemas críticos com o VLOOKUP, para que você tenha uma ideia das limitações dessa função e de quando usar INDEX e MATCH.

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 é mais “Ethan”. Isso ocorre porque o VLOOKUP é uma fórmula 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

Podem ocorrer situações em que você precise pesquisar valores de uma coluna (Emp ID #) que não seja a mais à esquerda, como mostrado abaixo.

Nesse caso, mover o Emp ID # para torná-la a coluna 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 MATCH e INDEX vem em seu socorro. Veja a seguir a sintaxe, 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 um 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 ajuda a evitar os erros que podem ocorrer com o VLOOKUP.

Função MATCH

Farei um breve resumo da função MATCH aqui para aqueles que não estão familiarizados com ela. A função MATCH recebe uma determinada search_key, que é o valor que você está dizendo para procurar em um determinado intervalo. Ao contrário da função INDEX, esse valor precisa ser unidimensional (em apenas uma coluna ou linha).

A função MATCH fornece a localização do item que você disse para ele procurar. Para search_type, você normalmente diz 0, o que informa à função que precisa ser uma correspondência exata.

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 fórmula 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 (os três primeiros exemplos na captura de tela acima). Não é de surpreender que 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.

Caso de uso #3: função INDEX com duas 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). Em seguida, 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. Saiba mais sobre Função MATCH do Google Sheets aqui.

Você gostou desta postagem?

Se você achou este artigo útil, compartilhe-o com sua rede por meio dos botões de mídia social à esquerda!