É possível usar Index e Match ou algo similar no Microsoft Excel para encontrar a última linha de número preenchida para dois conjuntos de números na mesma coluna?

Question

Exemplo de imagem da tabela:

Objetivo:Naimagemdatabeladeexemplo,queroextrairdacolunaC,últimonúmeroCSTpreenchidocomo1235eúltimonúmerodeVatpreenchidocomo204.

Seeuusar=VLOOKUP("CST", B2: C5,2, FALSE), ele retorna o primeiro valor de CST, ou seja, 1234. Da mesma forma, o VAT usando VLOOKUP retorna 203.

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

Screenshot (usando MATCH com lookup_value diferente na mesma coluna):

    

Answer 1

Para algo como isto, prefiro AGGRAGATE para 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))

Eu coloquei os critérios reais em D1 e D2 para que eu pudesse referenciá-los diretamente e não codificar "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 de tipo de matriz. Ele é definido de A2 para a última célula na coluna A que possui uma cadeia de texto.

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

    

Answer 2

Você pode fazer isso com LOOKUP .

LOOKUP Quando lookup_value é maior que qualquer número na matriz, a função retorna o último número na matriz; a construção 1/(1/(...)) converte os 0 em erros, portanto, o último "número" será o valor na mesma posição que o último CST ou VAT dependendo 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 a Nota Fiscal No é sempre um número, se pode ser uma string, a fórmula precisará de alguma modificação.

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 o VLOOKUP para pesquisas verticais. O segundo argumento é uma matriz. Se não houver limitação nas linhas, não especifique os números das linhas, apenas as letras das colunas. O terceiro argumento diz qual coluna usar. 2 significa o segundo, ou seja, C na sua matriz.

Por padrão, a função não pára na primeira localização; ele se move para encontrar mais ocorrências, resultando, em última análise, na localização da entrada mais inferior (normalmente a mais nova), exatamente como desejado.

Portanto, use

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

e

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

Note que também há o HLOOKUP para pesquisas horizontais.