# How do I find the address of last non-blank cell in a row (while skipping columns)

Basically, I need to do what the title says on my spreadsheet here. So for row 3, I need to find the address of the last non-blank cell in that row, while skipping the grayed out columns (with the header "Total"). If your data is in columns A:J (i.e., 1 to 10), and columns to be ignored are C and F (3 and 6), then for row 2 you would use

``````=ADDRESS(ROW(A2),SUMPRODUCT(MAX((A2:J2<>"")*(COLUMN(A2:J2))*(1-(COLUMN(A2:J2)=3))*(1-(COLUMN(A2:J2)=6)))))
``````

Explanation:

1. `ADDRESS` builds the address, taking the row number (known here) and the column number (what you mean to get).

2. `SUMPRODUCT(MAX((A2:J2<>"")*(COLUMN(A2:J2)) ... ))` picks the maximum column number for non-blank cells.

3. `... *(1-(COLUMN(A2:J2)=3))*(1-(COLUMN(A2:J2)=6))` makes the formula ignore specific columns (3 and 6 here). You should add other similar factors if needed.

Copy the formula downwards. Note that you might need to use some absolute references.

This also works

``````=ADDRESS(row(A2),INDEX(MAX((A2:J2<>"")*(COLUMN(A2:J2))*(1-(COLUMN(A2:J2)=3))*(1-(COLUMN(A2:J2)=6))),0))
``````

https://stackoverflow.com/a/19915043/2707864

1. this can be solved with a middle step (side calculation):

``````=TRANSPOSE(QUERY(QUERY({O60; N60; J60}, "select *"),
"select Col1 where Col1 is not NULL"))``````

• selected columns `{O60; N60; J60}` needs to be in reverse alphabet order

## _______________________________________________________________

2. then you can get absolute reference with this formula:

``=CELL("ADDRESS", INDEX(J60:60, MATCH(J60, J60:60, 0), 1))``

## _______________________________________________________________

3. and if you want to drop absolute reference, you need to wrap it into `REGEXREPLACE()`:

``=REGEXREPLACE(CELL("ADDRESS", INDEX(J60:60, MATCH(J60, J60:60, 0), 1)), "\\$", "")``

## _______________________________________________________________ 