Search code examples
arraysgoogle-sheetsgoogle-sheets-formulamatchrow

Get the value of the cell below regardless of sorting in Google Sheets

In Google Sheets, I would like to enter a formula that gets the value of the cell directly below it regardless of sorting. The extra wrinkle is that the cell I'm entering the formula into is not always in the same place after sorting.

So, if I'm entering the formula in cell A1, I want to get the value of A2. But, if I re-sort, and that cell is now in A5, I want it to give me the value of A6. Regardless of where I move target cell or the cell with the formula, I always want to reference the cell positioned directly below.

I feel like maybe some combination of ADDRESS and INDIRECT, but I'm having a hard time wrapping my brain around this one.

Solution

  • try:

    =INDIRECT(ADDRESS(ROW()+1, COLUMN()))