Find range of last used cell in row, calculate distance and offset to display cell contents of first cell in the found range's column

0
votes

This may be an iterative of this question. This would be an easy VBA task, but I believe this is more than doable using formulae. I have a general idea of how to achieve this but may be overthinking, I'm sure there is a more elegant way to do this than what I have so far.

I have a row of dates in row 1 containing values such as "1/31/2021", "2/31/2021", and so on. Below this are text values -- some cells are blank, others have basic text like "abc123" (there will never be a scenario where there are blank cells between text cells, so false positives are not a concern). My goal is to find the range of the last used cell in a row, then display the cell contents of the first cell in that column to find an appropriate date. What I have so far manages to find the last used row, and I am able to use offset to display the appropriate date, but I am looking for a solution that is scaleable, so that the formula can be used in a series of descending rows and will always correctly calculate the distance between the last used cell in each row and the contents of the first row in the result's column.

Below is what I have to find the last used cell in a row:

=INDEX(2:2,MATCH("zzzz",2:2))

This is what I have to find the last used cell in a row and offset to the cell in row 1 in the found column. This works for just row 2 as the distance between row 2 and row 1 is known. It would be ideal to offset by a dynamic value to always find the first cell in the column:

=OFFSET(INDEX(2:2,MATCH("zzzz",2:2)),-1,0)
1
google-sheets
which are you using Google sheets or Excel They are different, please remove the unneeded tags. – Scott Craner
I am testing on Sheets, but I believe the example formulae should work in Excel as well. The issue is germane to both, so tags for both are used. Removed Excel tags per your request to reflect the spreadsheet solution I am personally using. – Majora
There are uses of formula that work in one that will not work in others and vice versa. As well as there are whole formula that do not exist in one that does in the other. In this case yes the answer I gave will most likely work in sheets, but be careful in the future as that is not always the case. – Scott Craner
This makes sense, thank you. – Majora

1 Answers

1
votes

To find the first cell in the last column of a specific row:

=INDEX($1:$1,0,MATCH("zzzz",2:2))

This will return the value in row 1 in the last column with a value in row 2.