CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaworksheet-function
Pierre Maoui
Pierre Maoui

Reputation: 6384

Get the corresponding non-empty adjacent cell in a column in Google Sheets

I'm trying to find the formula for column "C" in order to obtain this:

A B C
1 blue flower blue
2 water blue
3 sky blue
4 green grass green
5 frog green
6 yellow lemon yellow
7 sun yellow

I've tried with INDEX and MATCH but I haven't found yet the way to go.

Bonus: a unique formula with ARRAYFORMULA would be very nice for my use case.

Upvotes: 2

Views: 577

Answers (3)

Erik Tyler
Erik Tyler

Reputation: 9345

Given the exact layout and ranges shown in your post, delete everything from Col C and place this array formula in C1:

=ArrayFormula(IF(B2:B="",,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},A:A<>""),2,TRUE)))

This will lookup each row number for rows where Col B is not empty within a virtual array of only those row numbers where Col A is not empty paired with the value of Col A, and return the result from the second column of that array. Because the final parameter of VLOOKUP is TRUE, any exact row number searched and not present in the limited virtual array will "fall backward" to the last row number that was occupied.

Upvotes: 2

Mike Steelson
Mike Steelson

Reputation: 15318

Another way

=ArrayFormula(if(B2:B="",,lookup(row(A2:A),row(A2:A)/if(A2:A<>"",1,0),A2:A)))

Upvotes: 0

marikamitsos
marikamitsos

Reputation: 10573

I guess you are looking for this

=ArrayFormula(if(row(A1:A) <= max(if(not(isblank(B1:B)), row(A1:A))),vlookup(row(A1:A),filter({row(A1:A),A1:A},len(A1:A)),2),))

Upvotes: 0

Related Questions