Como evito que o VLOOKUP quebre depois de adicionar uma coluna a um intervalo nomeado?

Question

Minha planilha do Excel contém muitos VLOOKUPs em um intervalo nomeado que eu defini. Agora, quando adicionei uma coluna no meio do intervalo nomeado, as VLOOKUPs que fazem referência a colunas após a coluna inserida agora estão quebradas. Eu entendo o problema, mas qual é a melhor maneira de corrigir? Existe uma maneira de descobrir o número da coluna no texto do cabeçalho?

[EDITAR] Usar a ideia de Kaze da INDEX e da MATCH parecia ser a melhor solução. Aqui está o que acabei com:

INDEX(MyTableData, MATCH("RowLabel", RowList, 0), MATCH("ColumnLabel", ColumnList,0))

em que MyTableData é um intervalo nomeado da tabela inteira, RowList é um intervalo nomeado do cabeçalho da linha e ColumnList é um intervalo nomeado dos cabeçalhos da coluna.

    

Answer 1

Pessoalmente, prefiro usar uma combinação INDEX-MATCH para pesquisas em vez de VLOOKUP . Aqui está um exemplo da Pokedex que eu estava compilando para minha sobrinha.

ParaobteraHiddenHabilidadedeSquirtle,euusariaessafórmula:

=INDEX(F2:F11,MATCH("Squirtle", A2:A11,0),1)

Isso produz o mesmo resultado que:

=INDEX(A1:G11,MATCH("Squirtle",A1:A11,0),MATCH("Hidden Ability",A1:G1,0))

Uma coisa boa sobre o INDEX-MATCH é que na maioria dos casos, você não precisa referenciar todo o intervalo de dados, portanto a primeira fórmula deve funcionar mesmo se você adicionar regularmente colunas e linhas ao seu intervalo de dados. Ele também tem outra vantagem: como você está referenciando apenas dois intervalos unidimensionais, ele calcula mais rápido.

No entanto, você ainda pode usar MATCH com suas fórmulas VLOOKUP para obter o número da coluna.

=VLOOKUP(C1,NAMED_RANGE,MATCH("COLUMN_TEXT",$A$1:$H1,0),0)

em que:% $A$1:$H$1 é a primeira linha em seu intervalo de dados / nome ou a linha que contém o texto de cabeçalho em NAMED_RANGE é o nome do intervalo de dados em que "COLUMN_TEXT" é o nome texto de cabeçalho para a coluna que contém os dados de que você precisa
C1 contém seu valor de pesquisa

Edit: Exemplo de Index-Match adicionado de acordo com o pedido de Doug. : D

    

Answer 2

Você pode usar a função MATCH :

Este site tem uma boa explicação de como usá-lo: link

Uma alternativa é usar a função COLUMN para ler o número real da coluna (se a sua tabela começar de qualquer lugar que não seja a coluna A, será necessário subtrair o número da primeira coluna da fórmula). Então, exemplo:

 =VLOOKUP(B2,$B$2:$D$300,COLUMN($D$1)-Column($B$1), False)

Onde a coluna D contém seu valor de pesquisa

    

Answer 3

Uma solução alternativa é criar uma função definida pelo usuário, que deve facilitar o processo.

Aqui está uma abordagem, que cria uma função chamada XLOOKUP :

Public Function XLOOKUP(ByRef row As Variant, ByRef column As Variant, ByRef table_array As Variant) As Variant
    XLOOKUP = CVErr(xlErrNA)
    Dim xRow As Long, xCol As Long
    With table_array
        For xRow = 1 To .Rows.Count
            If .Cells(xRow, 1).Value = row Then
                For xCol = 1 To .columns.Count
                    If .Cells(1, xCol).Value = column Then
                        XLOOKUP = .Cells(xRow, xCol).Value
                        Exit Function
                    End If
                Next
                Exit Function
            End If
        Next
    End With

End Function

Depois de adicioná-lo ao seu projeto, você pode usá-lo assim:

=XLOOKUP(RowLabel, ColumnLabel, Range)

    

Answer 4

Você também pode simplesmente definir seu vlookup original usando a função COLUMN () para a coluna com o valor a ser retornado. Uma versão simples tem a coluna de valor de pesquisa da sua tabela na Coluna A e o valor a ser retornado está em qualquer outra coluna, coluna L. Então use:

COLUMN (L: L)

para o valor a retornar da coluna. Mais complicado, como na coluna de pesquisa não é a coluna A? Basta subtrair do acima: (para pesquisa na coluna C)

COLUNA (L: L) -2

O valor aqui é que adicionar e excluir colunas não quebra sua fórmula, pois o Excel a corrige automaticamente. Adicione uma coluna? Torna-se COLUMN (M: M). E assim, para excluir uma coluna. Tudo tratado pelo Excel. E isso é realmente fácil de fazer. Colunas como "WYO"? O Excel não se importa. Encontre o número da coluna da sua coluna de pesquisa com uma fórmula COLUMN () rápida nessa coluna e, em seguida, você terá o valor a ser subtraído. Fácil.

Sem dúvida, menos poderoso do que o Index / Match e a prática faz a perfeição lá, então isso o impede. Mas é um resultado rápido que dura. E você pode ensiná-lo rapidamente a outras pessoas enquanto aprimora suas habilidades de Índice / Correspondência. Também não é tão intuitivo quanto usando intervalos nomeados, mas muitas vezes você não pode (tabelas alguém?). (E pode ser o valor subjacente para o intervalo nomeado, para esse assunto.)

Então, se você quer um rápido, simples, difícil de quebrar com simples alterações de coluna (ele manterá sua referência se cortado e reinserido em outra coluna, em vez de olhar relativamente de sua nova posição, mas para esse tipo de trabalho, Eu acho que é uma vantagem definitiva), então esta é uma solução para pensar.

    

Answer 5

Se você nomear os intervalos de coluna em sua fórmula de pesquisa, as fórmulas ainda funcionarão bem quando você adicionar ou excluir colunas.