match values not order

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » MATCH values not in order

MATCH values not in order

resolvedResolved · Medium Priority · Version 365

Benedict has attended:
Excel Advanced course

MATCH values not in order

Hello, I'd like to know if there is any way I can use a MATCH formula - or something similar - to return a value when the lookup array isn't in ascending/descending order.

I have a grid of four rows, one row for each quarter and against three brands, and the values in the grid refer to the start dates of each quarter for each brand. In my line of work, quarter start dates don't adhere strictly to the calendar month. This is a rolling grid in that once I become aware of the next quarter start date, I overwrite last year's start date with this year's. In the below example, I know the Q1 2023 start date, but have not yet received the Q2 2023 start date, so the 2022 values are still in place.

;BRAND 1 ;BRAND 2 ;BRAND3
Q1;03/01/2023;05/01/2023;04/01/2023
Q2;01/04/2022;02/04/2022;01/04/2022
Q3;02/07/2022;02/07/2022;01/07/2022
Q4;04/10/2022;04/10/2022;05/10/2022

I also have a cell with =TODAY() in. I need to be able to identify the most recent quarter start date in a given column that is equal to or less than today's date, however, because the rolling grid will only ever be in ascending order for one quarter a year (Q4, once all dates for the year are known) the MATCH formula is unable to produce the result I need. Is there any solution to this?

Thanks,

Benedict

RE: MATCH values not in order

Found the solution in MAXIFS(QUARTER DATES,QUARTER DATES,"<="&TODAY())

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Counting Blanks

Some times you want to check if there are cells missing data in your range. You can use the COUNTBLANK FUNCTION to acheive this. It is =COUNTBLANK(Range). Note Cells with formulas that return "" (empty text) are also counted. Cells with zero values are not counted.

View all Excel hints and tips


Server loaded in 0.5 secs.