0

Is there a way to refer to columns in a literal array like so:

=FILTER({lengthy_arrayformula_1, lengthy_arrayformula_2}, condition referencing column 2 of the array literal in previous argument)

or

=INDEX({lengthy_arrayformula_1, lengthy_arrayformula_2}, row, 2nd col of the literal array)

without creating 'buffer columns' that hold intermediate results.

1
0

Try query(), like this:

=arrayformula( 
  query( 
    { to_pure_number(Sheet1!A2:F), regexreplace(trim(Sheet2:A2:A)), "a", "b") },
    "where Col2 < 42", 
    0 
  ) 
)

See the query() help page for more info.

3
  • I tinkered with QUERY a bit. I am somewhat familiar with the syntax, but I don't get the idea behind the data array in your answer. Could you break it down and explain the role of TO_PURE_NUMBER and REGEXREPLACE here? And why is the query wrapped in ARRAYFORMULA? Jul 25 at 19:06
  • Although I have marked this is the answer, I am not yet quite sure it does what I want. I wish to extract that column and manipulate it for example as an input to a formula. column identifiers like Col1 or Col2 can only be used inside the WHERE clause of the query statement. Is there a way to use them as input to other formulas, like for example perform a VLOOKUP on them? Jul 25 at 19:12
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Jul 25 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.