CODEWITHSUNDEEP
excel-formula
ChrisB
ChrisB

Reputation: 3215

Approximate Match Without Sorting

I have a column of dates in column A sorted in ascending order. Cell C1 contains a date. I want a formula to find the earliest date in column A that is equal to or greater than the date in C1.

I realize this can be done by sorting column A in descending order and using the following formula...

=MATCH(C1,A:A,-1)

...but curiosity has me wondering if it's possible without changing the sort order. The formula above returned an error when the worksheet was sorted in ascending order.

Any ideas?

Upvotes: 0

Views: 1225

Answers (3)

user23008651
user23008651

Reputation: 1

You can use

=MAXIFS(A:A,A:A,"<="&C1)

to identify the closest date

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152535

Use this:

=IF(ISNUMBER(MATCH(C1,A:A,0)),MATCH(C1,A:A,0),MATCH(C1,A:A)+1)

It test whether there is an exact match, if so it uses the exact match. Otherwise it uses the default and then moves down one row.

Or simplified:

=MATCH(C1,A:A)+IF(ISNUMBER(MATCH(C1,A:A,0)),0,1)

Or even simpler:

=MATCH(C1,A:A)+ISERROR(MATCH(C1,A:A,0))

enter image description here

Upvotes: 1

Seb
Seb

Reputation: 1230

If your data is sorted in ascending order, you can use a countif

=countif(A:A, "<" & C1)

just be sure your data is always sorted and the header may need to be added manually if it is text.

Upvotes: 1

Related Questions