¿Es posible utilizar Index and Match o algo similar en Microsoft Excel para encontrar la última fila numérica completa para dos conjuntos de números en la misma columna?

Question

Imagen de tabla de muestra:

Objetivo: en la imagen de la tabla de muestra, quiero extraer de la columna C, el último número CST completado como 1235 y el último número de IVA completado como 204.

Si uso =BUSCARV("CST",B2:C5,2,FALSE), devuelve el primer valor CST, es decir, 1234. Del mismo modo, para el IVA, el uso de BUSCARV devuelve 203.

Si intento Coincidir, funciona bien para CST, pero para el IVA, la misma fórmula con un valor de búsqueda más pequeño, digamos 250 (ya que los valores del IVA son inferiores a 250), da como resultado #N/A.

Captura de pantalla (usando MATCH con diferente valor de búsqueda en la misma columna):

Answer 1

Para algo como esto prefiero AGREGAR a COINCIDIR:

=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))

Puse los criterios reales en D1 y D2 para poder hacer referencia a ellos directamente y no codificarlos "CST"en "VAT"la fórmula, haciéndolo arrastrable.

Los dos $A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))establecen dinámicamente el rango de referencia, ya que se trata de una fórmula de tipo matricial. Se establece desde A2 hasta la última celda de la columna A que tiene una cadena de texto.

El Agregado devolverá el número de fila más grande (última fila) que coincida con los criterios del ÍNDICE.

Answer 2

Puedes hacer esto con LOOKUP.

LOOKUP Cuando lookup_valuees mayor que cualquier número de la matriz, la función devuelve el último número de la matriz; la 1/(1/(...))construcción convierte los 0's en errores, por lo que el último "número" será el valor en la misma posición que el último CSTo VATdependiendo de la 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)))

Las fórmulas anteriores suponen que el número de factura es siempre un número; si puede ser una cadena, la fórmula necesitará alguna modificación.

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

Utilice BUSCARV para búsquedas verticales. El segundo argumento es una matriz. Si no hay limitación en las filas, no especifique los números de las filas, solo las letras de las columnas. El tercer argumento indica qué columna utilizar. 2 significa el segundo, es decir, C en su matriz.

De forma predeterminada, la función no se detiene en la primera búsqueda; continúa para encontrar más ocurrencias, lo que finalmente resulta en encontrar la entrada más baja (normalmente es la más nueva), exactamente como se desea.

Por lo tanto, use

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

y

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

Tenga en cuenta que también existe BUSCARH para búsquedas horizontales.

Answer 4

Durante el día, le di la vuelta a las cosas, por así decirlo, y luego usé VLOOKUP(), cuando era posible y INDEX/MATCHcuando no. La parte importante es voltear la mesa y a continuación se muestra cómo hacerlo. Úselo como tabla para buscar lo que uno quiere buscar y cuando encuentre la primera aparición, encontrará la última aparición "real", tal como se encuentra en la tabla original ("real").

Entonces, la tabla de valores es A1:B22. Entonces:

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

Básicamente, encuentra el número de filas (22), suma 1 (23), luego crea una matriz de valores que son ese valor (23) menos una serie de valores desde 1 hasta el número de filas (22), lo que da una matriz. de filas a devolver: 23-1=22, 23-2=21, 23-3=20, etc. entonces 22, 21, 20, y así sucesivamente hasta 1. Entonces INDEX()devuelve las filas en orden inverso.

Hoy en día (2022), simplemente usaríamos XLOOKUP()y buscaríamos desde el final hasta el principio. Ciertamente, si se hace lo anterior, se usaría SEQUENCE()para generar las secuencias numéricas.