Excel: is there a function that returns a range for satisfying the matching condition?

Excel: is there a function that returns a range for satisfying the matching condition?

Assuming you have cat in cell c1, the following formula will return the first continuous matching range. Enter with ctrl+shift+enter

=COUNTIF(OFFSET(A1:A5,MATCH(C1,A1:A5,0)-1,0,MATCH(TRUE,OFFSET(A1:A5,MATCH(C1,A1:A5,0),0)<>C1,0)),cat)

If your data always start in A1 and you want the whole range matching the first value, all the way until the next value, use the following array formula (entered with ctrl+shift+enter):

=OFFSET($A$1,0,0,MATCH(TRUE,A:A<>A1,0)-1)

This works as an array formula. Press ctrl+shift+enter in the formula bar to evaluate it. I have tested it to work on this very simple case. I cant tell about more complex cases:

=INDIRECT(A1:A & MAX(ROW(A1:A5)*(A1:A5=cat)*(A2:A6<>cat)))

Edit: If you really want the range to be returned and not values, you just have to remove the INDIRECT but leave everything else inside it.

Excel: is there a function that returns a range for satisfying the matching condition?

Leave a Reply

Your email address will not be published.