Excel formula to get last non-empty cell value from a group of disjoint cells

Question

There are plenty of formula-based solutions for finding the last non-empty cell value from a column, row or continuous range in Excel, for example, for columns the most popular are =LOOKUP(2,1/(A:A<>""),A:A) and =LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A). This is nice, but it only works for continuous ranges. Sometimes, however, you need to do the same for a list of disconnected cells or ranges of cells instead of a single continuous range. So, if there was a hypothetical function FINDLASTNONBLANK(), its call would look like this:

For an arbitrarily long list of single cells

=FINDLASTNONBLANK(A1, D1, F1)
=FINDLASTNONBLANK(A1, D1, F1, X1)

For an arbitrarily long list of ranges

=FINDLASTNONBLANK(A1:A4, A10:A15)
=FINDLASTNONBLANK(A1:A4, A10:A15, A20:A25)

Obviously, there is no such ready-made formula, so I am wondering if there is a way to concoct a formula-based solution for that. No VBA UDFs.

So far I was thinking in the following direction: if I could write a formulation for joining disconnected cells/ranges into one range/array, then perhaps I could use the classic =LOOKUP(2,1/(A:A<>""),A:A) hack, something like this (assuming hypothetical formula JOINRANGES() or a hypothetical aggregation syntax {,}):

=LOOKUP(2,1/(JOINRANGES(A1:A4,A10:A15)<>""),JOINRANGES(A1:A4,A10:A15))
=LOOKUP(2,1/({A1:A4,A10:A15}<>""),{A1:A4,A10:A15})

So maybe there is a syntax in Excel to specify an array from a list of disjoint cells/ranges/arrays that I am not aware of? If there is, it should be backwards-compatible with at least Excel 2007.

I would be fine with the solution being limited to 1-dimensional data sources (row only, column only), does not have to work on 2-dimensional matrix. In fact, I would be fine with a solution only working on the disjoint data from the same row, not even a column.

Soupy

Might the ranges in question contain null strings (""), e.g. as a result of formulas in those cells? Or are any empty cells genuinely empty? – Humpback

Answer 1

This is an interesting topic, that has been discussed in several places (If I understood correctly the question). For example MATCH vs XMATCH: Binary Search to Find the Last Entry within a Range started by @JosWoolley from the Microsoft Tech community. Here is one of the solutions provided that works for both numeric and text values to get the last non-empty position of an array (formula 1). All the solutions provided here work for O365.

=XMATCH(TAKE(TOCOL(A:A,1),-1),A:A,,-1)

What it does is via TOCOL removes the empty cells, then get the last non-empty value via TAKE and does a reverse search via XMATCH.

Maybe a shorter approach (formula 2), but with less performance than formula 1.

=XMATCH(1,N(A:A<>""),,-1)

and to find the last non-empty element, just add INDEX to the previous approach (formula 3):

=INDEX(A:A,XMATCH(TAKE(TOCOL(A:A,1),-1),A:A,,-1))

or just (formula 4) which has a better performance than formula 3 and the LOOKUP approach from the question:

=TAKE(TOCOL(A:A,1),-1)

Here is a sample output:

All of the above formulas can be easily adapted to have multiple disjoint ranges via VSTACK for a single column. For example for formula 2:

=XMATCH(1,N(VSTACK(rng1,[rng2],...)<>""),,-1)

If you don't have the VSTACK/HSTACK functions available, you can use instead CHOOSE as follows for the formula 4 approach:

TAKE(TOCOL(CHOOSE(SEQUENCE(,n), rn1, rn2,...,rnN,3,1),-1)

where n is the number of disjoint ranges

For example, for two ranges

=TAKE(TOCOL(CHOOSE({1,2}, A1:A3,A5:A7),3,1), -1)

Rasorial

The question is about discontiguous ranges and finding the last non-empty value in those. Isn't your answer focused too much on just finding the last non-empty cell? – Groom

@Groom That is why I put a disclaimer in my response, the question is confusing, the OP mentioned a single dimension. Using disjoin range separately it is just to use VSTACK as input in my solution, so I don't see the difference. Unless I am missing something. It is difficult to provide an answer without sample data and expected output. The OP is looking for a range concat solution because the OP is trying to adapt the LOOKUP solution, but we don't know what is the real problem. – Rasorial

@Groom now I realized the OP refers to Excel 2007 version in the question, I just looked at the tags, so the solution only works for O365. For older version, probably the approach provided by P.b. with the last update. – Rasorial

Answer 2

UPD: (thanks to @P.b) No, stacking A1:A4:A10:A15 does not work as desired, it is reduced to A1:A15 by Excel, thus invalidating the solution listed below. Back to the drawing board.

Original message:

I think found the syntax I was looking for:

=LOOKUP(2,1/(A1:A4:A10:A15<>""),A1:A4:A10:A15)

All I need to do to join disjoint ranges is to keep listing them as pairs of colon-separated ranges.

I am assuming that for this trick to work on individual cells, not ranges, one needs to convert each cell to a single-cell range to prevent undesired bridging between disjoint cells:

=LOOKUP(2,1/(A1:A1:A5:A5:A10:A10<>""),A1:A1:A5:A5:A10:A10)

Works in Excel 2007.

Any more elegant solutions?

Soupy

you could do: =LOOKUP(2,1/(ISNUMBER(MATCH(ROW(A1:A15),{1,2,3,4,10,15},0))*(A1:A15<>""))),A1:A15) Which may require Ctrl-Shift-Enter for older versions. – Kantianism

Array of constants ({1,2,3,4,10,15}) means hardcoding the offsets, which will break as soon as a new column is inserted or existing column is deleted. Not the best. – Soupy

That is correct and why I did not put it as an answer, just throwing things out there. Ithink that you have found the only way to do it in 2007 and maintain the versatility of the ranges changing if needed. – Kantianism

short of writing your own UDF. – Kantianism

Stacking a range like A1:A4:A10:A15 will give false return if A6 would be the last non-empty in that row. Basically it reads A1:A15. – Wiatt

@P.b: Thank you, I checked, it is indeed the case. Back to the drawing board. – Soupy

Long shot: =LOOKUP(2,1/((ROW(A1:A15)>ROW(A1))*(ROW(A1:A15)<ROW(A5))+(ROW(A1:A15)>ROW(A10))*(ROW(A1:A15)<=ROW(A15))*(A1:A15<>"")),A1:A15) – Wiatt

See edit in my answer. I think that or Scotts reply here earlier are usable options. It's not as clean as the solution in Office 365, but it works – Wiatt

Answer 3

=TAKE(TOCOL(VSTACK(A1:A4,A10:A15),1,0),-1)

The 1 (TRUE) in TOCOL ignores all empty cells in the range. The 0 (FALSE) adds the values Row-wise (By column = false), so you can also take the value in the last row of any of the columns from given range.

TAKE(array,-1) returns the last value in the array.

This requires Office 365.

Edit: added VSTACK into my formula to be more ontopic, but still not compatible to excel 2007.

Compatibility to Excel 2007:

In older Excel you need to either hardcore the row numbers as an array using INDEX, or write an ugly bigger than - smaller than, + bigger than - smaller than

=LOOKUP(2,
          1/
          ((ROW(A1:A15)>ROW(A1))*
           (ROW(A1:A15)<ROW(A5))+
           (ROW(A1:A15)>ROW(A10))*
           (ROW(A1:A15)<=ROW(A15))*
           (A1:A15<>"")),
          A1:A15)

Or better:

=LOOKUP(2,
        1/ 
        (ISNUMBER(MATCH(ROW(A1:A15),ROW(A1:A4),0))+ 
        ISNUMBER(MATCH(ROW(A1:A15),ROW(A10:A13),0))*
        (A1:A15<>"")),
        A1:A15)

I think these require entering with ctrl+shift+enter

Wiatt

It does not answer the question of dealing with disjoint ranges/arrays and it does not work on older versions of Excel. – Soupy

For disjointed range: =TAKE(FILTER(A1:A15,ISNUMBER(MATCH(ROW(A1:A15),{1,2,3,4,10,15},0))*(A1:A15<>"")),-1) – Kantianism

Yes, or use VSTACK or HSTACK inside the formula to stack the ranges (may be easier to maintain/read). These are not compatible to excel 2007 – Wiatt

Answer 4

I can't think of anything 'smooth', as I suppose this was just something relatively hard back in Excel 2007. What would work though could be some boolean structure which requires you to add in all ranges as below:

Formula in B1:

=INDEX(A:A,MAX(ROW(A1:A4)*NOT(ISBLANK(A1:A4)),ROW(A8:A11)*NOT(ISBLANK(A8:A11)),ROW(A14:A18)*NOT(ISBLANK(A14:A18))))

I deliberately used just three random ranges with data between/after them for the purpose of demonstrating. The above would also take into consideration empty string values that may be the result of formulae. If you don't want to take these into consideration, I suppose the above could boil down to:

=INDEX(A:A,MAX(ROW(A1:A4)*(A1:A4<>""),ROW(A8:A11)*(A8:A11<>""),ROW(A14:A18)*(A14:A18<>"")))

Groom