UNIQUE Detecting First Empty Cell & Excluding Last Non-Empty Cell Just Above It

I’m using the following formula to list unique values in a column, excluding the last non-empty cell in the column.

=UNIQUE(AV6:INDEX(AV:AV, MATCH(2, 1/(AV:AV<>""), 1)-1))

It works great, as long as there is no data in cells below it. For example, it fails if there is an empty cell just below and then data below that (see screenshot below).

Is there an easy way to modify this formula that meets the following requirements?

1. Searches for the first empty cell in a column, starting with AV6.
2. AND excludes the first non-empty cell in the range above it, which would be from cell AV6 to the non-empty cell just above the first empty cell it detects, ignoring all data below that first empty cell it detects.
3. Provides the unique list of values, starting from AV6 and ending given the dynamic criteria I’ve listed above.

Here’s a screenshot example just to illustrate further how the formula needs to work. The numbers on the left are numbers I inserted to act as row numbers for reference…

>Solution :

Assuming no version constraints per your tags, the following formula will do what you seem to want:

=UNIQUE(
    LET(
        a, INDEX($AV:$AV, SEQUENCE(ROWS($A:$A) - 5, , 6)),
        b, MATCH(TRUE, a = "", 0),
        INDEX(a, SEQUENCE(b - 2))
    )
)

• The SEQUENCE argument in a ensures the returned array starts at the 6th row.
• b will return the first blank cell
• b-1 would be the preceding cell which would have a value
• b-2 would therefore exclude the last non-blank cell