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.4K 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. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more