CODEWITHSUNDEEP
google-sheets
J Steurs
J Steurs

Reputation: 171

Is there a reason why my ArrayFormula is not working in the other cells of my column?

In a Google sheet with form responses, I made an additional column where I want to look up from each submission if the value left of my new column already occurs in a range on another sheet.
So this is going to be a Vlookup formula finally. Unfortunately, I didn't make it to the Vlookup part yet because the ArrayFormula part is not working. I started off by looking to the cell value at the left with this formula, which worked, but the ArrayFormula part of it DOESN'T work.

=ArrayFormula(indirect(ADDRESS(ROW(), COLUMN()-1)))

I know that some functions don't work very well with ArrayFormula, But I don't see any reason here this should not work because it's only looking to its row and its column.

I hope the image shows the problem well enough

Snippet from screen

Upvotes: 0

Views: 6378

Answers (2)

JPV
JPV

Reputation: 27262

If you just want to repeat the values from the previous column (let's say column A, starting in row 2), you can try in column B (also in row 2)

=Arrayformula(if(len(A2:A), A2:A,))

Change range to suit. See if that helps?

UPDATE: To repeat the previous column (anywhere you input the formula) try (in row 2)

=offset(A1, 1, column()-2, rows(A1:A))

To 'limit' the output you can use any number instead of rows(A1:A) or replace it with COUNTA(A1:A)...

Upvotes: 1

player0
player0

Reputation: 1

you can do something like this, which will search for specific header across entire sheet and then return values of that column: 

=QUERY({INDIRECT("Sheet1!"&
 ADDRESS(1,       MATCH("job ID", Sheet1!1:1, 0), 4)&":"&
 ADDRESS(1000000, MATCH("job ID", Sheet1!1:1, 0), 4))}, 
 "select * where not Col1 matches 'job ID' and Col1 is not NULL", 0)

without sheet name:

=QUERY({INDIRECT(
 ADDRESS(1,       MATCH("job ID", 1:1, 0), 4)&":"&
 ADDRESS(1000000, MATCH("job ID", 1:1, 0), 4))}, 
 "select * where not Col1 matches 'job ID' and Col1 is not NULL", 0)

Upvotes: 1

Related Questions