Excel: busque el siguiente valor más alto/más bajo, por fecha

Question

En este ejemplo de datos que se enumeran por fecha (descendente), me gustaría poder buscar cuándo el valor fue por última vez mayor o menor que el valor actual, por fecha.

Si el mes actual es diciembre y el valor es 5, la última vez que fue menor fue en noviembre (4) y la última vez que fue mayor fue en septiembre (8).

Las soluciones que he encontrado hasta ahora usando MATCH/INDEX/etc devolverían el siguiente valor más alto como agosto (6) porque es el siguiente valor más cercano a 5. Me gustaría buscar en la lista por orden de fecha.

Esto se usaría para decir, "el valor de este mes es el valor más alto desde septiembre", por ejemplo.

Answer 1

Si uno tiene el FILTRO de fórmula de matriz dinámica:

=@FILTER(A3:A7,B3:B7>=B2)

y

=@FILTER(A3:A7,B3:B7<=B2)

---

si no, entonces usamos INDEX/AGGREGATE:

=INDEX(A:A,AGGREGATE(15,7,ROW(A3:A7)/(B3:B7>=B2),1))

y

=INDEX(A:A,AGGREGATE(15,7,ROW(A3:A7)/(B3:B7<=B2),1))

Answer 2

Esta fórmula matricial (CSE) casi resuelve el problema:

• Fórmula en la celda S16:

  {=INDEX($P$16:$P$21,MAX(IF(($Q$16:$Q$21<=$Q16),$Q$16:$Q$21,0),0))}
  

• En la celda T16:

  {=INDEX($P$16:$P$21,MIN(IF(($Q$16:$Q$21>=$Q16),$Q$16:$Q$21,0),0))}
  

NÓTESE BIEN

• Termine la fórmula conCtrl+Mayús+Entrar.

---

:Editado:

Me gustaría recomendar que esta solución de dos pasos encuentre el más alto/más bajo más cercano.

• Fórmula matricial (CSE) en la celda S20:

  {=INDEX($P$16:$P$21,MATCH(MIN(IF($Q$16:$Q$21>$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}
  

• Fórmula matricial (CSE) en la celda T20:

   {=INDEX($P$16:$P$21,MATCH(MAX(IF($Q$16:$Q$21<$Q16,$Q$16:$Q$21)),$Q$16:$Q$21,0))}
  

NÓTESE BIEN

• Termine la fórmula en S20 y T20 conCtrl+Mayús+Entrar.

• Puede ajustar las referencias de celda en la fórmula según sea necesario.