-1

Basically, I have this function that finds the last non-blank cell in a range while skipping columns I don't want it to search. The value is returned, but I want it to return the cell address (for something else I am trying to make work). Here is my formula:

=IF(ISERROR(INDEX({M5,N5,O5,P5,R5,S5,T5,U5,W5,X5,Y5,Z5,AA5,AC5,AD5,AE5,AF5,AH5,AI5,AJ5,AK5,AM5,AN5,AO5,AP5,AQ5,AS5,AT5,AU5,AV5,AX5,AY5,AZ5,BA5,BB5,BD5,BE5,BF5,BG5,BI5,BJ5,BK5,BL5,BN5,BO5,BP5,BQ5,BR5,BT5,BU5,BV5,BW5,BX5},MAX(({M5,N5,O5,P5,R5,S5,T5,U5,W5,X5,Y5,Z5,AA5,AC5,AD5,AE5,AF5,AH5,AI5,AJ5,AK5,AM5,AN5,AO5,AP5,AQ5,AS5,AT5,AU5,AV5,AX5,AY5,AZ5,BA5,BB5,BD5,BE5,BF5,BG5,BI5,BJ5,BK5,BL5,BN5,BO5,BP5,BQ5,BR5,BT5,BU5,BV5,BW5,BX5}<>"")*COLUMN(A5:BA5)))),"Error", (INDEX({M5,N5,O5,P5,R5,S5,T5,U5,W5,X5,Y5,Z5,AA5,AC5,AD5,AE5,AF5,AH5,AI5,AJ5,AK5,AM5,AN5,AO5,AP5,AQ5,AS5,AT5,AU5,AV5,AX5,AY5,AZ5,BA5,BB5,BD5,BE5,BF5,BG5,BI5,BJ5,BK5,BL5,BN5,BO5,BP5,BQ5,BR5,BT5,BU5,BV5,BW5,BX5},MAX(({M5,N5,O5,P5,R5,S5,T5,U5,W5,X5,Y5,Z5,AA5,AC5,AD5,AE5,AF5,AH5,AI5,AJ5,AK5,AM5,AN5,AO5,AP5,AQ5,AS5,AT5,AU5,AV5,AX5,AY5,AZ5,BA5,BB5,BD5,BE5,BF5,BG5,BI5,BJ5,BK5,BL5,BN5,BO5,BP5,BQ5,BR5,BT5,BU5,BV5,BW5,BX5}<>"")*COLUMN(A5:BA5)))))

marked as duplicate by user0, serenesat, Alex, Rubén, ale Jan 17 at 2:37

This question was marked as an exact duplicate of an existing question.

0
  • put your formula in some empty cell, let's say cell A100
  • use this formula where you need so to refer to cell A100

=CELL("ADDRESS", INDEX(A100, MATCH(A100, A100, 0), 1))

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