É possível usar Index and Match ou algo semelhante no Microsoft Excel para encontrar a última linha numérica preenchida para dois conjuntos de números na mesma coluna?

Question

Exemplo de imagem da tabela:

Objetivo: Na imagem da tabela de exemplo, desejo extrair da coluna C o último número CST preenchido como 1235 e o último número de IVA preenchido como 204.

Se eu usar =VLOOKUP("CST",B2:C5,2,FALSE) ele retornará o primeiro valor CST, ou seja, 1234. Da mesma forma para o IVA usando VLOOKUP retorna 203.

Se eu tentar Match, funciona bem para CST, mas para IVA a mesma fórmula com um lookup_value menor, digamos 250 (já que os valores de IVA estão abaixo de 250) dá #N/A como resultado.

Captura de tela (usando MATCH com lookup_value diferente na mesma coluna):

Answer 1

Para algo assim eu prefiro AGGRAGATE a MATCH:

=INDEX($B:$B,AGGREGATE(14,6,ROW($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))=D$1),1))

Coloquei os critérios reais em D1 e D2 para poder referenciá-los diretamente e não no código rígido "CST"e "VAT"na fórmula, tornando-a arrastável.

Os dois $A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))definem dinamicamente o intervalo de referência, pois esta é uma fórmula do tipo array. É definido de A2 até a última célula da coluna A que possui uma string de texto.

O Agregado retornará o maior número de linha (última linha) que corresponda aos critérios para o ÍNDICE.

Answer 2

Você pode fazer isso com LOOKUP.

LOOKUP Quando lookup_valueé maior que qualquer número do array, a função retorna o último número do array; a 1/(1/(...))construção converte os 0's em erros, então o último "número" será o valor na mesma posição do último CSTou VATdependendo da fórmula.

Last CST:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="CST")*$C$2:$C$999)))
Last VAT:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="VAT")*$C$2:$C$999)))

As fórmulas acima assumem que o número da fatura é sempre um número; se for uma string, a fórmula precisará de algumas modificações.

Last CST:  =LOOKUP(2,1/(($B$2:$B$999="CST")*ROW($B$2:$B$999)),$C$2:$C$999)
Last VAT:  =LOOKUP(2,1/(($B$2:$B$999="VAT")*ROW($B$2:$B$999)),$C$2:$C$999)

Answer 3

Use VLOOKUP para pesquisas verticais. O segundo argumento é uma matriz. Se não houver limitação de linhas, não especifique os números das linhas, apenas as letras das colunas. O terceiro argumento informa qual coluna usar. 2 significa o segundo, ou seja, C na sua matriz.

Por padrão, a função não para na primeira localização; ele segue em frente para encontrar mais ocorrências, resultando em encontrar a entrada mais baixa (normalmente a mais recente), exatamente como desejado.

Portanto, use

=VLOOKUP("CST", B:C, 2)

e

=VLOOKUP("VAT", B:C, 2)

Observe que também existe HLOOKUP para pesquisas horizontais.

Answer 4

Durante o dia, eu virava a mesa de cabeça para baixo, por assim dizer, e depois usava VLOOKUP(), quando possível e INDEX/MATCHquando não. O importante é virar a mesa de cabeça para baixo e a seguir mostramos como fazer isso. Use isso como tabela para procurar o que se deseja procurar e quando encontrar a primeira ocorrência, estará encontrando a última ocorrência "real", pois está na tabela original ("real").

Portanto, a tabela de valores é A1:B22. Então:

=INDEX(A1:B22,  ROWS(A1:A22)+1-ROW(INDIRECT("1:"&ROWS(A1:A22))),  {1,2})

Basicamente, ele encontra o número de linhas (22), adiciona 1 (23) e, em seguida, cria uma matriz de valores que são esse valor (23) menos uma série de valores de 1 ao número de linhas (22), fornecendo uma matriz de linhas para retornar: 23-1=22, 23-2=21, 23-3=20, etc. então 22, 21, 20 e assim por diante até 1. Então INDEX()retorna as linhas na ordem inversa.

Hoje em dia (2022), usaríamos apenas XLOOKUP()e pesquisaríamos do fim ao início. Certamente, se fizermos o acima, seria usado SEQUENCE()para gerar as sequências numéricas.