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

enter image description here


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



  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


Adapted from related answers




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)), "\$", "")



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.