Share via

Excel Match next largest values

Anonymous
2023-05-11T16:44:44+00:00

Hi,

I'd like to get the row positions of two dates, or the position of next greater for min date

and next smaller for max date ;

The greater is fine but my dates are in ascending order so the -1 argument does not work

is there a way around this? I've usually had a complete list if dates so an exact match was ok.

I tried using sort which works for numbers ;

MATCH(H3,SORT(E4:E9,,-1),1) sort descending and then look for lesser that even though you want greater, but this failed when tried with the dates, any ideas

Richard.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-05-11T17:12:37+00:00

    next greater

    =MATCH(MINIFS(E4:E18,E4:E18,">"&E2),E4:E18)

    next smaller

    =MATCH(MAXIFS(E4:E18,E4:E18,"<"&F2),E4:E18)

    1 person found this answer helpful.
    0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2023-05-11T16:57:09+00:00

    How about

    =XMATCH(E2, E4:E18, -1)

    and

    =XMATCH(E2, E4:E18, 1)

    1 person found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-11T18:43:17+00:00

    Hi,

    Love the Minifs version, this is the sort of thing i wanted to achieve but with dates;

    not had a go yet, will leave till tomorrow;

    OFFSET(F3,MATCH(MINIFS(F3:F10,F3:F10,">="&H1),F3:F10,0)-1,0,

    MATCH(MAXIFS(F3:F10,F3:F10,"<="&I1),F3:F10,0)-MATCH(MINIFS(F3:F10,F3:F10,">="&H1),F3:F10,0)+1)

    0 comments
  2. Anonymous
    2023-05-11T18:14:12+00:00

    Thanks for introducing me to Minifs, not used it before, will experiment.

    RD

    0 comments
  3. Anonymous
    2023-05-11T18:11:56+00:00

    Thanks, I always forget that x match and lookups don't need data sorted

    RD

    0 comments