Search code examples
google-sheetsoffsetgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

How to get a cell value from a range starting from last one

first of all excuse my poor English, I'll try to explain myself better as I can.

I find hard to explain and summarize my problem, but here is it:

I have a range of cells in a Google Spreadsheet (almost a column, name it A) with some values, mixed, all numerical, but with some cells with no values. Empty values may change over time. The right column (name it B) show a series of values, with decremental order. The rows of the table can change position automatically considering this last column, so if we put a value in the column A it may modify the sum of B column and then, the =SORT() formula will reorder rows and place it somewhere else.

A        B             C
                     5,2440

6,6000   6,8740
5,3442   6,7000
         6,4500
5,3000   5,2440
         5,2152
6,9000   5,0423
3,7600   4,9523
         4,8240
4,6745   4,6123

The question is that I need the cell in column C to show the value of the B column corresponding to the fourth cell with content starting from the last one. In this case, is the cell with value 5,3000 at column A, so, the result to show is 5,2440.

Consider if somebody set a value at last empty cell (5,0000, for instance), and the =SORT() reorder the list and I get this:

A        B             C
                     5,2348

6,6000   6,8740
5,3442   6,7000
         6,4500
5,3000   5,2440
5,0000   5,2348
         5,2152
6,9000   5,0423
3,7600   4,9523
4,6745   4,6123

Now, the fourth cell with a value starting from the last one is the one with 5,0000, so the cell in column C has to show 5,2348

I've tried to make it possible with =VLOOKUP(), and =MATCH(), but I know that may cause problems if there are same A values repeated (and in my big table there are many repeated values in that A column). I know how to make a =OFFSET() to get a value starting from a specific row, but I've no idea to how to tell Spreadsheet to count and look for something backward.

Thanks in advance, all ideas will be much appreciated

Solution 
  • =QUERY(A3:B, "select B where A is not null limit 1 offset "&COUNTA(
 QUERY(A3:B, "select B where A is not null", 0))-4, 0)

    0