Find first non-blank cell in range

  • I'm sure I'm not the first person to have this question but I haven't been able to find anything online to help me solve my problem. I have a table of data and I need to pull the data from the last non-blank row in one of the columns in my table. Sounds simple enough but that's where you would be wrong. Normally I would be fine using =INDEX(Table1[Column X],COUNTA(Table1[Column X])) to find the last non-blank row of data. The problem with is that the column that I need to pull data from in the table that I'm working with isn't truly blank. Instead, there's a formula which is telling it to check for a certain condition, if that condition is met then spit out a value else the value in that cell is "". Even though the value in that cell may be "", since there is a formula which tells excel how to populate that cell, when using INDEX and COUNTA, excel treats the cell as if it's not blank and returns the wrong answer. What I need to figure out is how to find the last row which contains something other than "". For example, let's say I have the following table:

    DateData 1Data 2Data 3Sum
    January 20201236
    February 202054615
    March 202028313
    April 2020

    In the example table, let's say I need to pull data from the "Sum" column. In the example, the sum column would be populated with =IF(SUM(Data 1:Data 3)>0,SUM(Data 1:Data 3),""). If I were to use =INDEX(SUM,COUNTA(SUM)) then excel would return "" because April 2020 shows "" and is not truly blank because there is a formula inside the cell. What excel SHOULD be returning is March 2020's value of 13.

  • If the values are numbers you can use COUNT rather than COUNTA, if the values are text use

    =LOOKUP(2,1/(E2:E10<>""),E2:E10)

    COUNT is exactly what I needed!! I realized after I posted this that I posted under the VBA section of the forum rather than the excel formulas section but thank you for providing me the answer that I needed, Fluff13.