excel-formula

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?

Solution

  • 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