0

Is there a simple in-built function to select adjacent values from the last non-null row for a certain column?

In the below example the last non-null value in Column A is "E", and I'd like to select the corresponding value "13" from the next column.

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
user2490003
  • 10,706
  • 17
  • 79
  • 155

3 Answers3

1

Try:
=VLOOKUP(INDEX(A:A,MAX((A:A<>"")*(ROW(A:A)))),A1:B,2,0)

Refer Selecting the last value of a column. There are 22 answers toi choose from.

I like =INDEX(I:I;MAX((I:I<>"")*(ROW(I:I)))). It is one of the shortest and it copes with blank rows.

Add VLOOKUP and you can get the value on the adjacent columns.

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
  • `VLOOKUP` could return the wrong value if there aren't unique values in column A. A slight tweak to your preferred solution will solve this: `=INDEX(A:B;MAX((A:A<>"")*(ROW(A:A))),2)` – Adam Stevenson Dec 02 '19 at 03:56
  • One works with the data available, but this is a point nicely made. – Tedinoz Dec 02 '19 at 21:36
1
=indirect("B" & max(ARRAYFORMULA(row(A1:A)*if(A1:A="",0,1))),true)

or

=indirect("B" & arrayformula(max(if(A1:A<>"",row(A1:A),0))),true)

or with offset

=offset(B1,ARRAYFORMULA(MAX(if(A:A="",0,row(A:A))))-1,0)
user11982798
  • 1,878
  • 1
  • 6
  • 8
1

try:

=QUERY(A3:B, "select B where A !='' offset "&COUNTA(A3:A)-1)

0

or:

=ARRAYFORMULA(VLOOKUP(INDIRECT("A"&MAX(IF(A2:A="",,ROW(A2:A)))), A2:B, 2, 0))

0

or:

=ARRAYFORMULA(VLOOKUP(INDEX(QUERY({A2:A, ROW(A2:A)}, 
 "where Col1 !='' order by Col2 desc"), 1, 1), A2:B, 2, 0))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • The first query does it for me-simplicity. Would you mind, for those less talented (such as myself), explaining the significance/role of `row(A2:A)` in the last example? – Tedinoz Dec 02 '19 at 21:53
  • sure, ROW(A2:A) in combination with ArrayFormula will create array of numbers per each row that was found so in this case it would be array of 2,3,4,5,6,7,8,etc,etc... and as per virtual array {} its on the 2nd position which is picked up as Col2 by QUERY. simply put its just a simple reverse of rows – player0 Dec 02 '19 at 22:03