CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
user2490003
user2490003

Reputation: 11920

Finding adjacent column values from the last non-null row of a certain column

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

Upvotes: 0

Views: 86

Answers (3)

user11982798
user11982798

Reputation: 1908

=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)

Upvotes: 1

player0
player0

Reputation: 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

Upvotes: 1

Tedinoz
Tedinoz

Reputation: 8142

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.

Upvotes: 1

Related Questions