Forum Discussion

Courtesy's avatar
Courtesy
Copper Contributor
Feb 29, 2020

What formula returns the value of a cell but takes as parameters the output of another formula?

I am trying to return the value of a cell in the last row where data was entered into column B.  I found a way to return the row number of the last row where column B was populated and I know that I want the value that is in column H. How can I tell Excel to give me the value in cell H:(value of last row populated)?

excel
Formulas and Functions

3 Replies

  • liverlarson's avatar
    liverlarson
    Brass Contributor
    Mar 01, 2020

    you could use INDIRECT for this. Be careful though, it's a volatile function, and if overused can become quite taxing on CPU. It would look like
    =INDIRECT("B"&[your function that returns row number])

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 29, 2020

    Courtesy If I understand you correctly, this would do it.

     

    =INDEX(H:H,AGGREGATE(14,6,(ROW(B:B)*NOT(ISBLANK(B:B))),1),1)

     

     It returns whatever is in H on the last populated row in B. In other words, if B10 is the last cell in B, it will give you the value of H10.

Resources