Find last filled cell in a group with gaps

Your formula will never achieve your initial goal.

Let's see =TAKE(FILTER(B2:B16,ISNUMBER(SEARCH("d",A2:A16))),−1,1) bit by bit.

SEARCH("d",A2:A16)) returns an Array {;;;1;;;;;1;;;;;1;}, meaning it has found the letter d in cells 4, 9 and 14. By the way, to make your formula portable, you should replace "d" with a reference to cell A20.

Then ISNUMBER(SEARCH("d",A2:A16)) searches for numbers in the SEARCH results, and returns an array {FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}. Note that it's not searching for numbers in column B, rather in the array {;;;1;;;;;1;;;;;1;}, hence the TRUE values at positions 4, 9 and 14.

Then FILTER(B,ISNUMBER(SEARCH("d",A2:A16))) extracts from B2:B16 the values in the positions of the TRUE values in the last result, hence {300;;Z} for your first example, and {300;Z;} in the second.

Finally, TAKE(FILTER(B2:B16,ISNUMBER(SEARCH("d",A2:A16))),−1,1) obtains the last item of the 3-item array, whether it be Z or 0 (which comes from an empty cell).

By contrast, the formula I proposed goes straight to the point.

=XLOOKUP($A20,FILTER($A2:$A16,ISNUMBER(B2:B16), "X"),FILTER(B2:B16,ISNUMBER(B2:B16), "X"),"",0,−1)

ISNUMBER(B2:B16) returns an array of logical values telling if there are numbers between B2 and B16: {TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE} (the second-last false is the Z).

Then, the first FILTER reduces that result to the items that are matching a TRUE, between A2 and A16:

{"A";"B";"C;"D";"A";"B";"C";"A";"B";"C"}

The second FILTER does the same between B2 and B16:

{100;1;50;300;200;3;50;200;2;50}

All is left for XLOOKUP is to find the value in A20 (D) in the search array (first filter), which it does at position 4 only, then from the second filter's results looks for values at the same position(s), and from the result array {300} returns the last item since the search type is last to first (-1). Voilà.

Did you pick up the nice Numbers features where you can click on any part of the edited formula and get intermediary results at the bottom of the window? Very handy when debugging nested fonctions.

Best of luck!

> I specified individual cells with gaps because there will be numbers in the other cells.

You're saying that you want the formula to skip blanks, but that the other cells will have values? So it's not blanks at all.

The big question is how do you want/expect to identify the cells you DO want it to use?

There is one approach where you manually specify the cells to compare, but that doesn't scale since you'd have to continually update it as you add more cells to consider. Is there some other mechanism? You don't show the adjacent cells, so I don't know if they follow some pattern, category, etc. that can be used as a filter.

For example, if the values you want to consider all have a common value in column A, you could use something like:

=TAKE(FILTER(B,ISNUMBER(SEARCH("Cost",A))),−1,1)

This two-part function first uses FILTER() to filter column B to select just the cells where it's corresponding column A contains the word "Cost".

This is then passed into TAKE(), which takes the last row of this filtered set, in other words, the last value in that matching set.

This is predicated on having a consistent index table that you can match against (e.g. Column A = "Cost"). Without seeing more of your data I can't tell if this is sufficient or not.

To make things much simpler, you should declare 1 header row and 5 footer rows to your table. Thus, these rows will not get in the way of actual data cell selection and the formulas will be MUCH simpler.

Alternatively (and especially if you had more than 5 categories), you could put your current date results in a separate table.

In B23 you can therefore use that formula, that you can propagate to C22:C26 with the yellow bubble.

=XLOOKUP($B23,FILTER($B,ISNUMBER(C), "X"),FILTER(C,ISNUMBER(C), "X"),"",0,-1)

It's a simple XLOOKUP where the search range is made of the cells of column B matching column D's cells that contain a number, and the return range is made of the cells of column D matching column D's cells that contain a number. Searching from bottom to top.

There are a few ways of doing this. One is via XLOOKUP():

=XLOOKUP(REGEX(".+"),B,B,"",2,-1)

First, the REGEX(".+") is interpreted as "any number of characters", thus excluding blank cells. This is passed in as the search term for XLOOKUP.

The parameters for XLOOKUP() take a search string (the non-empty REGEX()), where to search (the range in question, in this case column B), the results (in this case, the same range, but it could be different). This is followed by what to return for no matches (an empty string), and the '2' tells XLOOKUP to perform a wildcard search (thus honoring the REGEX(), and the -1 tells it to search from last-to-first, thus returning the last value in the range that isn't empty.

This is the description of XLOOKUP, which you an see if the Format pane is open when you type the formula:

XLOOKUP(search-value, search-range, return-range, if-not-found, match-type, search-type)

Thus, the first filter determines the search-range, i.e. a union of the cells of column B whose line have a numeric value in column D. XLOOKUP will do its search in this union.

The second filter determines the return-range, i.e. a union of the cells of column D whose line have a numeric value in column D (themselves), a union from where XLOOKUP will extract the required value (at the same index than in the search-range).