I'd need to retrieve the last non-empty cell in a column based on changes on the adjacent column.
In the example below, the yellow cells are my target. They are defined by a change in column A.
CodePudding user response：
Your task can be accomplished in two steps:
- determine the rows that contain the desired data
=index(query({A2:B20,ROW(A2:B20)},"select max(Col3) where Col2 is not null group by Col1 label max(Col3)''"))and assemble the result to a regular expression using formulaJOIN("|", -- query result --)
the result we have a string: 5|11|14|19
- use a new query to output the values from the previously found rows
=index(query({A2:B20,ROW(A2:B20)},"select Col1,Col2 where Col3 matches '"& join("|",query({A2:B20,ROW(A2:B20)},"select max(Col3) where Col2 is not null group by Col1 label max(Col3)''")) &"'"))
the range A2:B20 is used for the example, but the formula also works with the open range A2:B