Search code examples
google-sheetsmaxrowgoogle-sheets-formulaarray-formulas

How to use index function to return the value of the last cell that isn't blank

I am keeping a dynamically updated Google Sheets file of team performance where certain cells are updated by a team member, and there is a dashboard at the top that pulls only the most recent numerical values inputted. I need to find a formula for the dashboard section at the top to return the value of the last cell that isn't blank in a non-sequential selection. for example, the cells would be: B30, B43, B56, B69, B82, etc.

Is there a way to use CHOOSE, INDEX, or something similar to do so?

Solution

  • the answer is: don't use INDEX. for a last non-empty row use:

    =ARRAYFORMULA(INDIRECT("B"&MAX(IF(B:B<>"", ROW(B:B), ))))

    =IF(B294<>"", B294,
 IF(B273<>"", B273,
 IF(B252<>"", B252,
 IF(B231<>"", B231,
 IF(B210<>"", B210,
 IF(B189<>"", B189, 
 IF(B168<>"", B168,
 IF(B147<>"", B147,
 IF(B126<>"", B126, 
 IF(B105<>"", B105,
 IF(B84 <>"", B84,
 IF(B63 <>"", B63, ))))))))))))

    0