Find last filled cell in a group with gaps

> 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.

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.